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
– 182764125216, Commented Jan 12, 2011 at 20:22
what are you going to do if this select StatusID from Y queries more than one row of data? — VoodooChild
– VoodooChild, Commented Jan 12, 2011 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
– OMG Ponies, Commented Jan 12, 2011 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? — Cloudy
– Cloudy, Commented Jan 12, 2012 at 16:03

RichardTheKiwi · Accepted Answer · 2011-01-12 20:31:45Z

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, 2011 at 20:31

answered Jan 12, 2011 at 20:26

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Madam Zu Zu Over a year ago

no it's sql, but where do i use my select to get the right data out from table Y?

Madam Zu Zu Over a year ago

i ended up making a temp table with the query above, and updating my table with your suggestion, thanks

user554546 · Accepted Answer · 2011-01-12 20:23:11Z

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, 2011 at 20:23

user554546

Comments

bobs · Accepted Answer · 2011-01-12 20:24:54Z

2

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

answered Jan 12, 2011 at 20:24

bobs

22.3k12 gold badges71 silver badges81 bronze badges

Comments

andbi · Accepted Answer · 2011-01-12 20:24:30Z

1

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

answered Jan 12, 2011 at 20:24

andbi

4,4547 gold badges51 silver badges70 bronze badges

1 Comment

Madam Zu Zu Over a year ago

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

Lamak · Accepted Answer · 2011-01-12 20:35:06Z

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, 2011 at 20:35

answered Jan 12, 2011 at 20:24

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

2 Comments

Madam Zu Zu Over a year ago

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....

Lamak Over a year ago

@xrum Is there a reason for all the RIGHT JOINS?, cause eventually you can have sourceid NULL on the output of your SELECT

Collectives™ on Stack Overflow

Update with a Select

5 Answers 5

2 Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related