Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using IF without an ELSE condition in SQL

I'm trying to use the below query to update some data only if @rowcount is greater than 0. However, its executing the update statement even when @RowCount is 0. Can someone help whats wrong in here please? I would like to do nothing if @RowCount is 0.

I'm using SQL Server 2014.

    TRUNCATE TABLE Count1
    DECLARE @RowCount AS INT

    --insert data in a temporary table
    SELECT YEAR, S_ID
    into #Count1 FROM
    (SELECT DISTINCT D.YEAR, S_ID FROM SALES S JOIN TRANSACTIONS PT
    ON S.COMBINED_TXN_ID = PT.S_ID AND PT.TRANSACTION_TYPE = 'BILLING'
    JOIN DATE D ON D.DAY = S.DAY AND PT.DAY = S.DAY
    WHERE
    S.SALES_CUSTOMER    !=   PT.CUSTOMER)Counter1;

    --Store the rowcount in a temporary variable
    SET @RowCount = (SELECT Count(*) FROM #Count1) 

    --Fix the data with differences only if count>0
    IF @@ROWCOUNT > 0
    BEGIN
    UPDATE SALES
    SET SALES_CUSTOMER =  PT.CUSTOMER
    FROM SALES S
    JOIN TRANSACTIONS PT ON  S.COMBINED_TXN_ID = PT.S_ID
    JOIN DATE D ON D.DAY = S.DAY AND PT.DAY = S.DAY
    WHERE
    S_ID IN (SELECT S_ID FROM #Count1) 
    END;
like image 334
Newbie Avatar asked Oct 25 '25 08:10

Newbie


1 Answers

@@ROWCOUNT Returns the number of rows affected by the last statement.

Change system variable @@ROWCOUNT by your own variable @RowCount

--Store the rowcount in a temporary variable
SET @RowCount = (SELECT Count(*) FROM #Count1) 

--Fix the data with differences only if count>0
IF  @RowCount > 0
like image 62
Indent Avatar answered Oct 26 '25 23:10

Indent