Friday, November 14, 2008

SQL Update Multiple Columns

Below Sql use to update multiple column for a table in 1 shot

*****************

UPDATE Table1
SET (Col1, Col2, Col3, Col4) =
(SELECT Col1a, Col2a, Col3a, Col4a FROM Table2 WHERE Col5a=Table1.Col5)
WHERE Col5 IN (SELECT Col5a FROM Table2 )

*****************

8 comments:

Avin said...

does not work

Stretching said...

WoW nice blog buddy!Actually WoW is my favorite appreciation keyword.I also like to play. Wow Gold. I was looking for info regarding that and saw your website. Its nice and very interesting.

Martin said...

Your query doesn't seem to work. I tried it in SQL Server with no luck. What version did you write it with?

Martin Fister - owner of
Office Lamps
Stainless Steel Tea Kettle
Luggage With Wheels

badsign said...

This query is for Oracle and possibly other databases that support 'row value constructors'. To make this work on SQL Server, try the following:

UPDATE Table1
SET Col1 = a.Col1, Col2 = a.Col2, Col3 = a.Col3, Col4 = a.Col4 FROM
(SELECT Col1a, Col2a, Col3a, Col4a FROM Table2 WHERE Col5a=Table1.Col5) AS a
WHERE Table1.Col5 IN (SELECT Col5a FROM Table2 )

badsign said...

For a more 'chatty' explaination of my above comment, I posted a blog entry on GeeksWithBlogs.net.

Sunny said...

try this one....

update student
set st_id =
(CASE
WHEN st_id = 'St1' THEN 'STU1'
WHEN st_id = 'St2' THEN 'STU2'
WHEN st_id = 'St3' THEN 'STU3'
ELSE st_id END);

Ankur Kumar said...

I am Having A Table with 5 Coloums How To Set and Update REcords in that at one hit
Please MAke me clear it with good example

Ankur Kumar said...
This comment has been removed by the author.