Update Query with a JOIN

[Roobmeister]

Hi everyone,

Let's say I want to update a table based on a join with another table. In SQL Server, this is easy. I can just write my update query this way:

UPDATE upt
SET upt.name = s.lastname, upt.address = s.street
FROM clients upt
INNER JOIN census s ON upt.social = s.ssn AND upt.zip = s.zipcode

In this SQL Server example, everything in the "clients" table updates fine, and the tables are joined on two values.

This is so simple, but I have no idea how to do this in Oracle PL SQL?

Thanks in advance!

[amitpatel66]

Try this:

[code=oracle]

UPDATE upt x SET (name,address) = (SELECT lastname,street FROM census WHERE ssn = x.social AND zipcode = x.zip);

[/code]

[Roobmeister]

thank you

thank you very much, Amit..

That works perfectly. I have one more question, though.

How do I delete from one table based on a join on two values?

In my previous example, written for sql server, this would look like:

DELETE upt
FROM clients upt
INNER JOIN census s ON upt.social = s.ssn AND upt.zip = s.zipcode

I only want to delete the records in the clients table that are returned from this inner join. How do I write this for Oracle?

Thanks

[amitpatel66]

Try this:

[code=oracle]

DELETE FROM clients WHERE (ssn,zipcode) IN (SELECT social,zip FROM social);

[/code]

[Roobmeister]

Thank you, that works perfectly!

[amitpatel66]

We are happy to help :).
Do post back in case of any further issues

Moderator