Update table based on another table

Question

I'm trying to update a column in a table based on another column in another table.

UPDATE eval e
   SET rank = (SELECT p.desc
                 FROM Position p
                WHERE p.id = e.faculty 
                  AND p.date >= '2011-05-20'
              )

p.id and e.faculty correspond. I want to update rank with p.desc if the id's are the same. (e.faculty and p.id)

Any help will be great! :)

marc_s · Accepted Answer · 2011-05-16T21:19:39.270

22

Try this for SQL Server:

UPDATE dbo.eval 
SET rank = p.desc
FROM dbo.Position p
WHERE p.id = eval.faculty and p.date >= '2011-05-20'

or if you need an alias on the base table (for whatever reason), you need to do this:

UPDATE dbo.eval 
SET rank = p.desc
FROM dbo.eval e
INNER JOIN dbo.Position p ON p.id = e.faculty 
WHERE p.date >= '2011-05-20'

edited May 16 '11 at 21:19

answered May 16 '11 at 21:02

marc_s

732,580
175
1,330
1,459

Is there a way to use this syntax with an alias on the updated table? For example, this is invalid: `UPDATE @Y SET Field = X.Field FROM @X AS X WHERE @Y.Key = X.Key`. I'd normally use `UPDATE Y SET Field = X.Field FROM @Y AS Y INNER JOIN @X AS X ON Y.Key = X.Key`, but that's a little more verbose. – May 16 '11 at 21:18
This should work for Postgresql, too, as a non-standard extension. – Andrew Lazarus May 16 '11 at 23:47
"update banks_info set state = s.guid FROM banks_info b inner join bank_states s on s.name = b.state" I am trying this query, but not able to run this. "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM banks_info b inner join bank_states s on s.name = b.state' at line 1" any suggestions – Chetan Sharma Apr 14 '13 at 03:34
1

I like the second query more, because you only change SELECT with UPDATE, so there is lower probability to make an error because most people I guess use JOIN by default so you don't need to rewrite it to old join for update reason. – Muflix Mar 02 '17 at 09:09

score 2 · Answer 2 · answered May 17 '11 at 13:25

You need a restriction in the form of a WHERE clause; if you use EXISTS you can based it on you scalar subquery e.g.

UPDATE eval
   SET rank = (
               SELECT p.desc
                 FROM Position p
                WHERE p.id = eval.faculty 
                      AND p.date >= '2011-05-20'
              )
 WHERE EXISTS (
               SELECT *
                 FROM Position p
                WHERE p.id = eval.faculty 
                      AND p.date >= '2011-05-20'
              );

Note the above targets the UPDATE on the base table eval rather than the correlation name e. This makes a lot more sense when you think of an SQL UPDATE in terms of relational assignment i.e. you don't want to assign to e because it (unlike the base table) will go out of scope!

Update table based on another table

2 Answers2

Linked

Related