CODEWITHSUNDEEP
mysqlsqlselectsql-update
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

Update with a Select

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

Upvotes: 2

Views: 1334

Answers (5)

Lamak
Lamak

Reputation: 70638

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?

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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

Upvotes: 3

bobs
bobs

Reputation: 22184

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

Upvotes: 2

andbi
andbi

Reputation: 4454

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

Upvotes: 1

user554546
user554546

Reputation:

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.

Upvotes: 2

Related Questions