How to associate two Mysql tables with update in Oracle, oraclemysql

How to associate two Mysql tables with update in Oracle, oraclemysql

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;

I constructed a table in MySQL to verify it.

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 provide the update operation associated with the two tables. If the above statement is entered, 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. line id = test1.id * 1st error: ORA-00971: Missing SET keyword

Later, I checked the official documents and found that this syntax is not supported. How can I 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); two rows have been updated. SQL> select * from test; ID SALARY ---------- 1 400 2 500 3 300