MS SQL Server allows UPDATE and DELETE statements to include a FROM … INNER JOIN … clause in the form
UPDATE Foo SET blah = 1 FROM Foo INNER JOIN Bar ON Foo.ID = Bar.FooID ...
This syntax is not supported in Oracle databases. I found this blog which offers a different approach by updating a JOINed subselect:
UPDATE (SELECT blah FROM Foo INNER JOIN Bar ON Foo.ID = Bar.FooID SET blah = 1
I found, however, that you need to have UPDATE grants on all joined tables or views. In my case, I needed to copy data from the meta-model views, such as USER_TAB_COLUMNS, which understandably do not allow updates.
The other solution I found was to retrieve the updating values via a subselect, such as:
UPDATE Foo SET blah = (SELECT Bar.blah FROM Bar WHERE Foo.ID = Bar.FooID)
requiring a single-record result set for each of the updated records, and a subselect for each column.
The case of DELETing records based on a JOINed expression can be dealt with subselects, or the WHERE (…) IN (SELECT …) clause:
DELETE Foo WHERE Foo.ID = (SELECT FooID FROM Bar ...)
or
DELETE Foo WHERE ID IN (SELECT FooID FROM Bar...)
or
DELETE Foo WHERE (ID, ID2) IN (SELECT FooID, FooID2 FROM Bar...)
Thanks! Just what I was looking for.