CODEWITHSUNDEEP
sql-serversql-update
Sam
Sam

Reputation: 29009

How to change SQL column value if it is not empty?

This is trivially easy when I just need to change one column:

UPDATE [MyTable] SET [MyColumn1] = 'NewValue' WHERE [MyColumn1] <> ''
...
UPDATE [MyTable] SET [MyColumn20] = 'NewValue' WHERE [MyColumn20] <> ''

My poblem is, I need to update not just one column, but a dozen. And I'd like to update them all in one UPDATE command instead of firing a separate command for every one.

Is there a way to do this in one update command?

Upvotes: 0

Views: 57

Answers (4)

Dgan
Dgan

Reputation: 10285

try like this

Loop through sys.columns and Get Column Name and Respective Table Name 

DECLARE  @COlname varchar(MAX),@TableName varchar(MAX)
DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

select name,d.TableName from ( select OBJECT_NAME(OBJECT_ID) 
TableName,* from sys.columns )d
where d.TableName='tbl_Name'

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName

   INTO @COlname,@TableName



WHILE @@FETCH_STATUS = 0

BEGIN

  print  'UPDATE '+ @TableName +'SET '+@COlname +
  '= ''NewValue'' WHERE '+@COlname +' <> '''' '

 EXEC ('UPDATE '+ @TableName +'SET '+@COlname +
  '= ''NewValue'' WHERE '+@COlname +' <> '''' ')


   FETCH NEXT FROM cursorName

   INTO @COlname,@TableName



END

CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

Upvotes: 0

Manthan Amin
Manthan Amin

Reputation: 21

This is not possible in single query

but you can try using procedure also by passing table name and values to a parameter.

Upvotes: 1

Hitesh
Hitesh

Reputation: 3498

Try this 

update MyTable set 
mycolumn1 = case when [MyColumn1] <> '' then 'NewValue' else [MyColumn1] end,
mycolumn20 = case when [MyColumn20] <> '' then 'NewValue' else [MyColumn20] end...

just keep adding the columns below which you want to update

Upvotes: 1

John Woo
John Woo

Reputation: 263713

use CASE, something like this below

UPDATE t 
SET MyColumn1 = CASE WHEN MyColumn1 <> '' THEN 'NEW VALUE' ELSE MyColumn1 END,
    MyColumn2 = CASE WHEN MyColumn2 <> '' THEN 'NEW VALUE' ELSE MyColumn2 END
FROM MyTable t
-- WHERE '' NOT IN (MyColumn1, MyColumn2)

Upvotes: 1

Related Questions