I came across a situation, where I had to write a different sort of update statement. Following was the user requirement.
UPDATE two columns A and B in a table, say MY_TABLE based on the following condition.
DECLARE @UPDATEQUERY VARCHAR(1000) ;
DECLARE @COMMA VARCHAR(3) ;
SET @UPDATEQUERY = 'UPDATE MY_TABLE SET'
SET @COMMA = ' '
IF (SET OF FIRST CONDITIONS )
BEGIN
SET @UPDATEQUERY = @UPDATEQUERY + ' A = '+ @var1 --COLUMN A IS ASSIGNED VALUE OF VARIABLE @VAR1
SET @COMMA = ' , ';
END
IF (SET OF SECOND CONDITIONS)
BEGIN
SET @UPDATEQUERY =@UPDATEQUERY+@COMMA
SET @UPDATEQUERY = @UPDATEQUERY + ' B= ''Y'' '--ASSIGNING COLUMN B A VALUE 'Y'
END
SET @UPDATEQUERY = @UPDATEQUERY + ' WHERE C = '+ @var2
--CHECKING IF ANY OF THE TWO COLUMNS IS BEING UPDATE THEN EXEC THE DYNAMIC QUERY.
IF(CHARINDEX('A',@UPDATEQUERY,1 )<>0 AND CHARINDEX('B',@UPDATEQUERY,1 )<>0 )
EXEC(@UPDATEQUERY)
ELSE
SET @UPDATEQUERY = NULL;
UPDATE two columns A and B in a table, say MY_TABLE based on the following condition.
- Update column A of MY_TABLE if a certain set of conditions met else dont update it
- Update column B of MY_TABLE if a second set of certain conditions are met else dont update it
DECLARE @UPDATEQUERY VARCHAR(1000) ;
DECLARE @COMMA VARCHAR(3) ;
SET @UPDATEQUERY = 'UPDATE MY_TABLE SET'
SET @COMMA = ' '
IF (SET OF FIRST CONDITIONS )
BEGIN
SET @UPDATEQUERY = @UPDATEQUERY + ' A = '+ @var1 --COLUMN A IS ASSIGNED VALUE OF VARIABLE @VAR1
SET @COMMA = ' , ';
END
IF (SET OF SECOND CONDITIONS)
BEGIN
SET @UPDATEQUERY =@UPDATEQUERY+@COMMA
SET @UPDATEQUERY = @UPDATEQUERY + ' B= ''Y'' '--ASSIGNING COLUMN B A VALUE 'Y'
END
SET @UPDATEQUERY = @UPDATEQUERY + ' WHERE C = '+ @var2
--CHECKING IF ANY OF THE TWO COLUMNS IS BEING UPDATE THEN EXEC THE DYNAMIC QUERY.
IF(CHARINDEX('A',@UPDATEQUERY,1 )<>0 AND CHARINDEX('B',@UPDATEQUERY,1 )<>0 )
EXEC(@UPDATEQUERY)
ELSE
SET @UPDATEQUERY = NULL;