A simple way to update two tables simultaneously in an Oracle database _oracle

I used to write simple UPDAET statements, such as Updae table set c1= ' XXX '

There is a problem with data revisions today, the project background is as follows, there is a table A, there are two fields A1,a2 also has an associated table B, which also has two fields, B1 and B2. Where A2 and B2 are associated and want to update the fields in a A1 to B1 in B

Theoretically SQL should be very good to write, but in the oralce implementation of a half-day has been reported grammatical errors. And there are some small details that are not noticed.

Test the data first

Table 1,zz_test1

Table 2,zz_test2

To update the text of table one to the Text1 value of table two, the corresponding SQL is as follows:

Update Zz_test1 t1 set T1. " Text "= (
 select T2.) Text1 "from Zz_test2 T2 where T2." pid "=t1." ID "
) 
where EXISTS
(
 SELECT 1 from Zz_test2 T2 where T2.) pid "=t1." ID "
)

The following where condition represents a constraint, updates only those data that match the criteria, and can be written as

Update Zz_test1 t1 set T1. " Text "= (
 select T2.) Text1 "from Zz_test2 T2 where T2." pid "=t1." ID "
)
where T1." Id ' in (SELECT ' PID ' from Zz_test2)

There is also a form of merge, the corresponding SQL is as follows:

Merge into Zz_test1 T1 using Zz_test2 t2 on (t1. " ID "=t2." pid ") when 
 
matched then
 
update set T1." Text "=t2." Text1 "

To avoid having multiple data in the T2 corresponding to the data in the T1, you can change the SQL to the following ways:

MERGE into Zz_test1 t1 USING 
( 
select * from Zz_test2 x WHERE x. ROWID = 
(select MAX (y.rowid) from Zz_test2 Y WHERE X. " id "= Y." ID ")
 ) 
T2 on 
(t1.) id "= t2." pid ") when 
matched THEN
UPDATE SET t1." Text "= t2." Text1 "

There is also an update from syntax that has been tested in Oracle and MySQL and is not applicable

To sum up, try to update a field in a table to one of the fields in the other table. You can use the update syntax and be qualified. The syntax for the merge is used, and the syntax for the merge is also available, and update from cannot be used in Oracle or MySQL.