UPDATE and DELETE with JOINed Tables in Oracle

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...)

Advertisements