In Oracle, if two tables are updated, the SQL statement is written in this way, and Oracle does not pass. After checking the information, you must write the SQL statement in this way.
In Oracle, if two tables are updated, the SQL statement is written in this way, and Oracle does not pass. After checking the information, you must write the SQL statement in this way.
Prerequisites:
The info_user table contains the field id and name, and the field id is an index.
The data_user_info table contains the field id and name, and the field id is an index.
The field id in the info_user table is the same as the field id value in the data_user_info table.
Implementation requirements:
Update the name of the field in the info_user table to be consistent with that in the data_user_info table.
The SQL statement is as follows:
Update info_user I set (I. name) = (select d. name from data_user_info d where d. id = I. id)
Where I. id = (select d. id from data_user_info d where d. id = I. id );
Commit;
------------------------- Split line -------------------------
In Oracle, if an update is performed across two tables, the SQL statement is written as follows:
Update Table a set a. ID = Table2.ID where a. Name = Table2.Name
Oracle won't pass. I checked the information. The SQL statement must be written in this way.
Update Table a set a. ID = (select B. ID from Table2 B where B. Name = a. Name) where exist (select 1 from Table2 B. Name = a. Name)
You can also update multiple fields.
Update Table a set. ID = (select B. ID from Table2 B where B. name =. name),. code = (select B. code from Table2 B where B. name =. name) where exist (select 1 from Table2 B. name =. name)