SQL Server - UPDATE table where ID is in SELECT?

I have a table where I want to update all rows with the ID that exists in the select result.

My pseudo-code:

UPDATE mytable as t
   SET t.status = 'PM'
 WHERE t.ID EXISTS IN (select ID from ...)

开发者_运维百科

I have managed to do the select statement, now I want to use the result of the select statement to update a table.

---

If you remove the exists you have a valid query from what I can tell.

UPDATE mytable 
   SET status = 'PM'
 WHERE id IN (select ID from ...)

Works for me in MySql 5.5, not sure which database you're using.

---

One cannot use substitution in the UPDATE statement. The original query should be good when you leave out the " as t" part and both "t.".

---

That works for me

#dont work
UPDATE mytable as t1 SET t1.a = value 
WHERE t1.b IN (SELECT b FROM table WHERE condition);

#work
UPDATE mytable as t1 SET t1.a = value
WHERE t1.b IN (SELECT * FROM (SELECT b FROM table WHERE condition) as t2)