A simple method to update two tables at the same time in the Oracle database.

A simple method to update two tables at the same time in the Oracle database.

Previously, I only wrote some simple updaet statements, such as updae table set c1 = 'xxx '.

Today, I encountered A data correction problem. The project background is as follows: Table A has two fields a1 and a2, and an associated table B. There are also two fields, b1 and b2. A2 is associated with b2, and we want to update field a1 in A to b1 in B.

In theory, SQL should be quite easy to write, but a syntax error has been reported for a long time in oralce. Some small details have not been noticed.

First, test the data.

Table 1, ZZ_TEST1

Table 2, ZZ_TEST2

To update the text of Table 1 to the text1 value of table 2, the corresponding SQL statement 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 indicates a restriction condition. Only the data that meets the condition can be updated and can be written

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 )

In addition, there is a merge statement, which corresponds to the following SQL:

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 multiple data entries in T2. you can change the SQL statement to the following method:

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 THENUPDATE SET t1."text" = t2."text1"

There is also an update from syntax, which has been tested and is not applicable in oracle and mysql.

To sum up, you need to update the fields of one table to a field in another table in the project. You can use the update syntax and make restrictions. The merge syntax is used, and there is also a merge syntax. The update from statement cannot be used in oracle or mysql.