Update with a Select

Question

How can I update StatusID in Table X using Table Y?

Table X has SourceID and old StatusID
Table 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 messes 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

What exactly are you trying to do? Populate a new column or keep data agreeing with each other? — 182764125216, Jan 12 '11 at 20:22
what are you going to do if this `select StatusID from Y` queries more than one row of data? — VoodooChild, Jan 12 '11 at 20:25
What you posted is the most supported means of updating a table based on the value from another. MySQL and SQL Server are the only databases I'm aware of that support JOINs in UPDATE syntax. — OMG Ponies, Jan 12 '11 at 20:26
Is there any reason you can't back up the database, run it, and then restore the backup if everything goes badly? — yoozer8, Jan 12 '12 at 16:03

RichardTheKiwi · Accepted Answer · 2011-01-12T20:31:45.277

3

This could be simpler

update x
set StatusID= Y.StatusID
from Y
where y.SourceID = X.SourceID

If it is Access, then you could use

update x inner join y on y.sourceid=x.sourceid
set x.statusid = y.statusid

edited Jan 12 '11 at 20:31

answered Jan 12 '11 at 20:26

RichardTheKiwi

105,798
26
196
262

no it's sql, but where do i use my select to get the right data out from table Y? – Madam Zu Zu Jan 12 '11 at 20:35
i ended up making a temp table with the query above, and updating my table with your suggestion, thanks – Madam Zu Zu Jan 12 '11 at 21:03

score 2 · Answer 2 · answered Jan 12 '11 at 20:23

2

I'm not sure that works. Try:

update x set StatusID=Y.StatusID
from Y where (x.SourceID=Y.SourceID);

ETA: This should work in PostgreSQL, but I'm not sure about other SQL dialects.

answered Jan 12 '11 at 20:23

score 2 · Answer 3 · answered Jan 12 '11 at 20:24

2

update x
set StatusID = y.StatusID
from x
join y on x.SourceID= y.SourceID

answered Jan 12 '11 at 20:24

bobs

21,844
12
67
78

Lamak · Answer 4 · 2011-01-12T20:35:06.320

1

UPDATED
In SQL Server you can do this:

UPDATE A
SET A.StatusID= B.StatusId
FROM TableX AS A
JOIN TableY AS B
ON A.SourceID = B.SourceID

In your updated question, now you are just doing a SELECT, it's not gonna update any record at all. What database enginge are you using?

edited Jan 12 '11 at 20:35

answered Jan 12 '11 at 20:24

Lamak

69,480
12
108
116

SQL 2005. the Select that i am showing gets the SourceID and the new ActionID that i then need to update table X with. but i can't figure out how to do it.... – Madam Zu Zu Jan 12 '11 at 20:36
@xrum Is there a reason for all the `RIGHT JOINS`?, cause eventually you can have sourceid NULL on the output of your `SELECT` – Lamak Jan 12 '11 at 20:47

score 1 · Answer 5 · answered Jan 12 '11 at 20:24

1

update x, y 
  set x.StatusID=y.StatusID 
  where x.SourceID=y.SourceID

answered Jan 12 '11 at 20:24

andbi

4,426
5
45
70

I am using joins to get the StatusID for table X, so i think i need to use a select. – Madam Zu Zu Jan 12 '11 at 20:25

Update with a Select

5 Answers5