The query below does NOT work for this conditional update. Update data in table A based on two or more common columns in table B. Updates based on two or more common columns are normally used for tables where multiple columns work together as a primary key known as composite primary key.
These columns uniquely identify a record in a table. Happy updating! Get single records when duplicate records exist 2. Find duplicate values or non-duplicate values in a table 3. How the data types in Access Northwind are converted to Oracle 6. All Rights Reserved. Viewed 1. Table 1: id name desc 1 a abc 2 b def 3 c adf Table 2: id name desc 1 x 2 y In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using the same id?
So the end result I would get is Table 1: id name desc 1 x 2 y 3 c adf Question is taken from update one table with data from another , but specifically for oracle SQL. Laxmi 3, 22 22 silver badges 30 30 bronze badges. Muhd Muhd You need to go back to your other question, un-accept that answer, and state specifically that you need the Oracle PLSQL syntax.
Oh I see. So you copy-pasted the question body, but modified to include the Oracle bit. And this probably isn't the best example since "desc" is a reserved word, but oh well.
Add a comment. Active Oldest Votes. Justin Cave Justin Cave k 22 22 gold badges silver badges bronze badges. Or do you use it only to speed up the query? Without it, every row in t1 will be updated and the values will be set to NULL if there is no matching row in t2.
FROM t2 must result in a unique row. This means that you have to select on all the fields which comprise a unique key -- a non-unique primary key is not sufficient. Without uniqueness, you are reduced to something like PaulKarr's loop -- and if there is not a unique correlation, then more than one target row may be updated for each source row.
Explanation on key-preserved requirement for updatable joins: asktom. RachitSharma - That means that your subquery the query from table2 is returning multiple rows for one or more table1 values and Oracle doesn't know which one you want to use.
Normally, that means that you need to refine the subquery so that it returns a single distinct row. Show 6 more comments. Adrian Adrian 6, 5 5 gold badges 26 26 silver badges 26 26 bronze badges.
Very fast indeed, rows merged in 15,5s — jefissu. I hope everybody visiting this question after notices this answer. I find that every time I need to do another merge I keep coming back to this answer for inspiration. I might print it out and frame it on my wall — arnehehe. Sandeep You might be interested in this stack-exchange proposal. It's almost ready to begin beta, just needs a few more. Add a comment. Active Oldest Votes. Depending on the declaration of primary key or unique constraints on both tables, you may be able to use the updateable inline-view method, which is probably more efficient: update select TABLE1.
Dave Costa Dave Costa Jon Heller Allan Allan COL2 this would work on Sql Server. Rockcoder Rockcoder 7, 3 3 gold badges 31 31 silver badges 41 41 bronze badges. Another Conditions Michael Pakhantsov Michael Pakhantsov Sign up or log in Sign up using Google. Sign up using Facebook.
0コメント