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;
@@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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With