Consolidating multiple updates on same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Consolidating multiple updates on same table

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2014-05-16 : 17:40:50

Can the following three separate update commands be combined into just once command?

UPDATE Mytable SET Fld1 = '' WHERE Fld1 IS NULL
UPDATE Mytable SET Fld2= '' WHERE Fld2 IS NULL
UPDATE Mytable SET Fld3= '' WHERE Fld3 IS NULL

What would be the optimal single command?

Thanks!

--PhB

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-16 : 18:29:16

Something like this should work:

UPDATE 
	Mytable 
SET 
	Fld1 = COALESCE(Fld1, '')
	,Fld2= COALESCE(Fld2, '')
	,Fld3= COALESCE(Fld3, '')
 WHERE 
	Fld1 IS NULL
	OR Fld2 IS NULL
	OR Fld3 IS NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-16 : 18:33:43

As a side note, depending on indexing and other factors, individual queries might be faster. Having OR predicates sometimes lead to table scans. So, just putting it all into one statement might not be the most efficient, if that is/was your goal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources