How to associate two Mysql table update operations in Oracle
When reading MySQL 5.1 reference manual, we found that MySQL provides a two-table join update operation. The original article is as follows: UPDATE items, month SET items. price = month. priceWHERE items. id = month. id; the constructed table in MySQL verifies
mysql> select * from test;+------+--------+| id | salary |+------+--------+| 1 | 100 || 2 | 200 || 3 | 300 |+------+--------+3 rows in set (0.00 sec)mysql> select * from test1;+------+--------+| id | salary |+------+--------+| 1 | 400 || 2 | 500 |+------+--------+2 rows in set (0.00 sec)mysql> update test,test1 set test.salary=test1.salary where test.id=test1.id;Query OK, 2 rows affected (0.00 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> select * from test;+------+--------+| id | salary |+------+--------+| 1 | 400 || 2 | 500 || 3 | 300 |+------+--------+3 rows in set (0.00 sec)
As you can see, only the rows with id 1 and id 2 in test are updated in the associated update of the two tables. After thinking about it, Oracle does not seem to have provided the update operation for the two tables, and the preceding statement is also input. Oracle Reports an error and the error message is as follows: SQL> update test, test1 set test. salary = test1.salary where test. id = test1.id; update test, test1 set test. salary = test1.salary where test. id = test1.id * 1st Line Error: ORA-00971: Missing SET keyword later, checked the official documentation, this syntax is not supported, so how to achieve this effect in MySQL in Oracle? It was a bit complicated, even though it came out. SQL> update test set test. salary = (select salary from test1 where test1.id = test. id) where exists (select 1 from test1 where test1.id = test. id); 2 rows have been updated. SQL> select * from test; ID SALARY ---------- 1 400 2 500 3 300