Oracle sql query update with join

Je suis confused. Fan of better aliases Just a point here, if your key trg. Marc In the ON , trg is the alias for the master table, table1 "outer" table by your logic , and src references the USING group "inner table" by your logic.

But yeah, probably could've been referenced better, but I was able to follow it. This can work in some cases, but the view has to be "key-preserved" every joined table has to be equality-joined on its primary key or otherwise unique fieldset.

This makes sure every record in the target table contributes to at most one record in the resulting rowset, and, hence, every record in the target table is updated at most once. Show 3 more comments. Roland Roland 6, 11 11 gold badges 55 55 silver badges bronze badges. This version is arguably cleaner, but it is not trigger friendly because there is no way I am aware of to avoid triggering update triggers for unchanged rows using this syntax. I am assuming that the triggers are needed for the changed rows.

Do not use some of the answers above. Also, ensure that there are primary keys on all the tables you are joining. Morten Anderson Morten Anderson 2, 14 14 silver badges 19 19 bronze badges. Alexis Dufrenoy Alexis Dufrenoy This doesn't work for me. It updates the entire table.

Can set multiple properties by adding a comma at the end of that. I needed to do t1. FirstName, t1. LastName on a table after matching it on the "UserName" column t1. The database was such where it was using UserName as a primary key to UserInfo everywhere, instead of placing FirstName and LastName in the table, directly. This fixed that! This answer adds nothing to the answer already provided by Quassnoi five years before yours. This following syntax works for me.

Jim Garrison Hemant Hemant 49 1 1 bronze badge. JimGarrison Please re-edit this answer so I can remove my downvote I was trying to use this syntax and it wasn't updating my table. I thought the syntax was merely updating a temp table instead of the real one, but I was wrong. Janek Bogucki Janek Bogucki 4, 2 2 gold badges 27 27 silver badges 39 39 bronze badges. Phil 1, 6 6 gold badges 22 22 silver badges 42 42 bronze badges.

Dan Anderson Dan Anderson 29 2 2 bronze badges. FlorinGhita just fyi, the merge query has been added — Sathyajith Bhat. I believe you want something like this. Justin Cave Justin Cave k 22 22 gold badges silver badges bronze badges. It's always helpful to post an example of your data and the expected output to make things more clear.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses.

Featured on Meta. In Oracle you can update a join if the tables are "key-preserved" , ie:. In your case since the updated value doesn't depend upon another table you could use a simple update with an EXIST condition, something like this:. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How to update with inner join in Oracle Ask Question. Asked 9 years, 10 months ago. Active 2 years, 1 month ago. Viewed 41k times. Update table t set t. Improve this question. Nil Pun Nil Pun