Update a table from another? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Update a table from another?

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2011-01-12 : 15:34:10

how can i update Status ID in table X using table Y?

X - has SourceID and old StatusID y - has SourceID and new StatusID

update x
set StatusID= (select StatusID from Y)
where
SourceID = (select SourceID from Y)
is this right? i'm afraid to run the query in case it messed everything up....

I am using joins to get the StatusID for table Y, so i think i need to use a select.

this is how i'm getting SourceID and StatusID for table Y

select t2.Sourceid, t3.ActionID
from tblSource t2
right join Y t1 on t1.BaselineSourceKey= t2.tempSourceID
right join lkuActionCode t3
on t3.actioncode = CASE
WHEN t1.actionCode = 'R' THEN 'N'
WHEN t1.actionCode = 'B' THEN 'R'
WHEN t1.actionCode = 'A' THEN 'R'
WHEN t1.actionCode = 'E' THEN 'N'
WHEN t1.actionCode = 'F' THEN 'S'
WHEN t1.actionCode = 'G' THEN 'S'
WHEN t1.actionCode = 'K' THEN 'DP'
WHEN t1.actionCode = 'Q' THEN 'C'
WHEN t1.actionCode = 'S' THEN 'AER'
WHEN t1.actionCode = 'T' THEN 'AEN'
WHEN t1.actionCode = 'U' THEN 'C'
WHEN t1.actionCode = 'V' THEN 'UR'
WHEN t1.actionCode = 'W' THEN 'R'
END
where actionid <> 10 and actionid <> 8 and actionid <> 3

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-12 : 15:49:47

Assuming that SourceID is unique...


UPDATE	x
SET	StatusID = y.StatusID
FROM	TableX x
JOIN	TableY y
On	x.SourceID = y.SourceID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources