Update one column with other column/table data

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 2005 Forums

Transact-SQL (2005)

Update one column with other column/table data

Author

Topic

inibhreaslain
Starting Member

19 Posts

Posted - 2009-03-23 : 15:32:56

I have two table. They both use a unique key LogID where t1.LogID = t2.LogID.

I wish to update some columns in t1 with values from columns in t2 AND some t2 columns with other t2 values.

ie...
UPDATE Table1 t1, Table2 t2
SET t1.col1 = t2.cola,
t1.col2 = t2.colb,
t2.cola = t2.colc,
t2.colb = t2.cold,
t2.colc = t2.cole,
t2.cold = t2.colf
where t1.LogID = t2.LogID
and t1.col1 is null

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-23 : 15:35:23

Use 2 update statements. One for each table.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-23 : 16:03:42

[code]
Update t1
SET t1.col1 = t2.cola
,t1.col2 = t2.colb
from
Table1 t1
inner join
Table2 t2
on t1.LogID = t2.LogID

Update t2
set t2.cola = t2.colc
,t2.colb = t2.cold
,t2.colc = t2.cole
,t2.cold = t2.colf
From
Table2 T2
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources