Update Query with a JOIN

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!

re: Update Query with a JOIN

Try this:

re: Update Query with a JOIN

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

re: Update Query with a JOIN

Try this:

re: Update Query with a JOIN

Thank you, that works perfectly!

re: Update Query with a JOIN

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

Moderator