DB2 Update coolumn based off of results from join?

[eaglei22]

I have two tables, A and B. I am trying to update a status field to, "CLS" only if there are two matching rows found between A and B.. I am stuck trying to do this in DB2.

I have tried,

UPDATE B
SET status = "CLS"
WHERE ORDERNO =

(SELECT b.ORDERNO​

FROM A a, B b​

WHERE a.ORDERNO = b.ORDERNO)​

Using the, "FROM" command like sql server uses does not work as the keyword, "FROM" is unexpected. I need to only update the matching rows in table B to "CLS" not all of them. Thank you.

 

[eaglei22]

Got it to work this way finally (and after pulling most of my hair out)

UPDATE B
SET status = "CLS"
WHERE ORDERNO IN

(SELECT b.ORDERNO​

FROM A a, B b​

WHERE a.ORDERNO = b.ORDERNO)​