Oracle中如何实现Mysql的少说明关联update操作

当看《MySQL
5.1参考手册》的下,发现MySQL提供了扳平栽点儿表关联update操作。原文如下:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

于MySQL中组织表验证了瞬间

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: 0

mysql> select * from test;
+------+--------+
| id   | salary |
+------+--------+
|    1 |    400 |
|    2 |    500 |
|    3 |    300 |
+------+--------+
3 rows in set (0.00 sec)

不难看出,上述两表明关联update中唯有更新了test中id为1及id为2底实施。

 

想了转,Oracle中仿佛并无提供简单说明关联的update操作,同样输入了上述话语,Oracle报错,报错信息如下:

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
           *
第 1 行出现错误:
ORA-00971: 缺失 SET 关键字

后来,查了查看官方文档,这种语法并无支持,那么Oracle中争落实MySQL中的这种效益也?

挑了一晃,虽然下了,但要么稍微为复杂性。

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行。

SQL> select * from test;

        ID     SALARY
---------- ----------
         1        400
         2        500
         3        300

 

相关文章