the second scenario is not a problem. I guess your query would work in MS SQL Server, Postgres etc. - but Oracle handles Updates (syntactically) in a different fashion: you could use a MERGE statement or a correlated subquery (with an additional exists). Personally I favor MERGE for these kinds of operations - something like:
merge into Table3 t3
using (select T1.id
from Table1 T1, Table2 T2
where T1.id = T2.id) src
on (t3.id = src.id)
when matched then update t3.Column1 = src.Column1
, t3.Column2 = src.Column2
, t3.Column3 = src.Column3
, t3.Column4 = src.Column4
(of course not tested...)
For scenario 1, you should write separate UPDATE statements for table2 and table3.
To guard against someone else changing any of the tables while you're doing that, you can copy all the relevant data into a global temporary table, and UPDATE table3 from that global temporary table.
COMMIT only after both tables have been changed.
You may want to write a procedure, or an INSTEAD OF trigger to do all of this.
For scenario 2, you can reference as many tables as you need to when updating table3. It might be more efficient and simpler to use MERGE rather than UPDATE. For example:
MERGE INTO table3 dst
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
ON (dst.id = src_id
WHEN MATCHED THEN UPDATE
SET dst.column1 = src.column1
, dst.column2 = src.column2
, dst.column3 = src.column3
, dst.column4 = src.column4
and the first scenario (updating multiple tables in a single query) is not possible in any RDBMS I know.
thinking again about it I would perhaps also favor the dst alias for table3 and even the ANSI join syntax...
I didn't mean to plagiarize. I was typing my suggestion (reply #3) when yours (reply #2) got posted.
I had not the slightest idea to imply something like that: I use to point colleagues to your SQL solutions in this forum frequently. I just thought it was funny that our queries were so similar - and rather took that as a sign that the code should work.
P.S. and my remarks on the dst alias and the ANSI syntax were not tongue in cheek.
No problem; I didn't take it as an accusation. I just wanted to acknowledge that you posted essentially the same solution first.
Thank you, Frank.
Now here is another thing.
I will need to UPDATE T3 by looping through all of the rows in T1 and T2 to find the values I need to set the fields in T3.
So, for example, If T1.Column3 has a value of 3, set T3.Column3 = T2.Column5 and T3.Column2 = T1.Column2
I need to do some verification in T1, so I can set T3 with data from table T1 and T2.
How do I go about accomplishing this by using this same MERGE statement that you provided?
Sorry, I don't understand.
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
If you're asking about a DML statement, such as UPDATE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using (e.g., 22.214.171.124.0).
See the forum FAQ: https://forums.oracle.com/message/9362002#9362002
Depending on what kind of verification you need to do on t1, you can add a WHERE clause to USING sub-query.
If t3.column3 gets set to t2.column3 in some situations, but it gets set to t2.column5 in other situations, then use a CASE expression.