how to update all columns of a 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 2000 Forums

SQL Server Development (2000)

how to update all columns of a table

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-06-25 : 07:43:32

hi all

i wanna to update all columns of a table using the id column
with select query
table1
id col1 col2 col3
1 x y z

table2
id common_column value_column
1 col1 a
1 col2 b
1 col3 c

now i want to table1 with value_column of table2

thanks in advance

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-25 : 08:15:21

You can use dynamic SQL to do this as long as you know what you want too update each column to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 08:17:14

quote:
Originally posted by DURGESH

hi all

i wanna to update all columns of a table using the id column

didnt get that. can you elaborate with some sample data?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 09:55:08

quote:
Originally posted by DURGESH

hi all

i wanna to update all columns of a table using the id column

Seems unusual. Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-26 : 02:08:32

Nice edit, but please refer to this:

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 11:26:31

quote:
Originally posted by DURGESH

hi all

i wanna to update all columns of a table using the id column
with select query
table1
id col1 col2 col3
1 x y z

table2
id common_column value_column
1 col1 a
1 col2 b
1 col3 c

now i want to table1 with value_column of table2

thanks in advance

UPDATE t1
SET t1.col1=t2.col1,
t1.col2=t2.col2,
t1.col3=t2.col3
FROM table1 t1
INNER JOIN (
SELECT id,
MAX(CASE WHEN common_column='col1' THEN value_column ELSE NULL END) AS col1,
MAX(CASE WHEN common_column='col2' THEN value_column ELSE NULL END) AS col2,
MAX(CASE WHEN common_column='col3' THEN value_column ELSE NULL END) AS col3
FROM table2
GROUP BY id)t2
ON t2.id=t1.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources