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

One Response to UPDATE and DELETE with JOINed Tables in Oracle

  1. virginia says:

    Thanks! Just what I was looking for.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: