Geeks With Blogs
Justin Gardner .NET, T-SQL and Reporting Services Developer

Hello SQL Server Readers,

You've probably seen multiple column update examples out there using syntax such as this:

set (col1, col2) = (select col1, col2 from ...)

You've also probably found that the above syntax is not SQL Server friendly. Apparently this is Oracle syntax and very well may work on other databases too, so long as they support 'row value constructors'.

To make this work on SQL Server you'll need to make a few changes. But don't fret, when you see the example below you'll probably kick yourself like I did because it makes perfect sense and is a very familiar concept to us SQL Server developers. 

From what I've read, this is what you'd call using the update statement extension. I'll just call it using an aliased subselect as that makes more sense in my mind. I never claimed to be uber technical.

One last thing before the example that you'll want to pay close attention to. In the outer where clause (if you choose to use one) be sure to qualify your column name. This may not be necessary if you're subselect does not have the same column names as your update statement. My test query did use the same column names and I couldn't get the query to run properly until I qualifed the outer where clause column. Now, without further ado, the example:

update table1
set col1 = a.col1, col2 = a.col2, col3 = a.col3 from
(select col1, col2, col3 from table2 where <expression>) as a
where table1.col1 <expression>

Good luck, and happy T-SQL.

Justin

Posted on Tuesday, October 13, 2009 10:11 AM SQL Server | Back to top


Comments on this post: Update Multiple Columns On SQL Server

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
Sorry... you totally lost me
Left by Dave on Oct 29, 2009 11:42 PM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
This was soooooo useful. Totally makes sense. Thanks!
Left by Different Dave on Nov 18, 2009 2:39 PM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
Just happened to stumble across this blog while researching another issue and thought I'd throw my slight variation out there:

(assuming you have a unique key to join on)

UPDATE table1 SET
column1 = a.column1,
column2 = a.column2,
column3 = a.column3
FROM table1
JOIN table2 a
ON table1.ID = a.ID

Left by cvg on Feb 23, 2011 4:42 PM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
i made all the appropriate changes but didn't work, then used cvg's suggestion - WORKED!
Thanks!
Left by newbie_prog on Mar 12, 2011 10:46 AM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
I've tried to use your example to update,but I am using fully qualified table name like [database].[schema].[table].[column]. And it gives me an error: "The multi-part identifier "[database].[schema].[table].[column]" could not be bound." by some reason?

What should do in this case?

Thanks
My email:dmitriy.mzhen@myfwc.com
Left by Dmitriy on Jun 16, 2011 7:23 AM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
MFer
Its not working
Left by shaitaan on Jun 24, 2011 5:30 AM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
Thanks. That works great.
Left by Herb R on Jun 30, 2011 11:20 AM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
Hey, thanks! Just what I needed.
Left by Brian on Jul 07, 2011 1:54 PM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
This worked great! Thanks.
Left by Jay on Nov 18, 2011 12:20 PM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
Thanks for the code. Exactly what I needed
Left by Jay on Nov 21, 2011 9:10 AM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
Thanks dear, it works great.... You saved my time... :)
Left by Devender Arya on Feb 15, 2012 1:00 AM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
Thanks dear,
It works and really usefull
Left by Deepak Mahajan on Aug 10, 2012 3:49 AM

# re: Update Multiple Columns On SQL Server
Requesting Gravatar...
That is really helpful to migrate from oracle to sql server 2008..Thanks Justin.
Left by Janaki on Sep 25, 2012 7:11 AM

Your comment:
 (will show your gravatar)
 


Copyright © Justin | Powered by: GeeksWithBlogs.net | Join free