Oracle兼容-语法-UPDATE … SET

1. 语法

  1. SET sql_mode = ORACLE;
  3. UPDATE [LOW_PRIORITY] [IGNORE] table_reference
  4.     SET (assignment_list) = (var_list)
  5.     [WHERE where_condition]
  6.     [ORDER BY ...]
  7.     [LIMIT row_count]
  9. UPDATE [LOW_PRIORITY] [IGNORE] table_reference
  10.     SET (assignment_list) = (SELECT stmt)
  11.     [WHERE where_condition]
  12.     [ORDER BY ...]
  13.     [LIMIT row_count]

需要先切换到 ORACLE 模式下才能支持本语法。

2. 定义和用法

在GeratSQL中，支持执行 UPDATE ... SET 同时更新多表，类似Oracle用法。主要有以下两种形式：

  • 用法1，多表更新

UPDATE t1, t2 ... tn SET(column1,column2) = (value1, value2)

这种写法支持同时更新多表中的同名多列。

  • 用法2，单表更新，更新值源自子查询

UPDATE TABLE t1 SET(a,b) = (SELECT * FROM t2) WHERE

这种用法只支持更新单表，且只支持单条SELECT语句赋值，不支持多个SELECT语句，与Oracle行为一致。

3. Oracle兼容说明

在Oracle中不支持上述提到的 用法1，即同时更新多表，只有在GreatSQL中才支持。

4. 示例

创建测试表并初始化数据：

  1. greatsql> CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(10) NOT NULL, c3 VARCHAR(10) NOT NULL);
  2. greatsql> CREATE TABLE t2 LIKE t1;
  3. greatsql> INSERT INTO t1 VALUES (1, 'rt1c2', 'rt1c3');
  4. greatsql> INSERT INTO t2 VALUES (1, 'rt2c2', 'rt2c3');
    1. 示例1：多表更新
  1. greatsql> SET sql_mode = ORACLE;
  3. greatsql> UPDATE t2, t1 SET (t2.c1, t1.c1) = (2, 9);
  4. Query OK, 2 rows affected (0.00 sec)
  5. Rows matched: 2  Changed: 2  Warnings: 0
  7. greatsql> SELECT * FROM t1;
  8. +----+-------+-------+
  9. | c1 | c2    | c3    |
  10. +----+-------+-------+
  11. |  9 | rt1c2 | rt1c3 |
  12. +----+-------+-------+
  14. greatsql> SELECT * FROM t2;
  15. +----+-------+-------+
  16. | c1 | c2    | c3    |
  17. +----+-------+-------+
  18. |  2 | rt2c2 | rt2c3 |
  19. +----+-------+-------+
    1. 示例2：更新值源自子查询
  1. greatsql> SET sql_mode = ORACLE;
  3. -- 这么写也是可以的
  4. -- UPDATE t1 SET (c1, c2 ,c3) = (SELECT * FROM t2 WHERE c1 = 2) WHERE t1.c1 = 9;
  5. greatsql> UPDATE t1 SET (t1.c1, t1.c2, t1.c3) = (SELECT t2.c1, t2.c2, t2.c3 FROM t2 WHERE t2.c1 = 2) WHERE t1.c1 = 9;
  6. Query OK, 1 row affected (0.00 sec)
  7. Rows matched: 1  Changed: 1  Warnings: 0
  9. greatsql> SELECT * FROM t1;
  10. +----+-------+-------+
  11. | c1 | c2    | c3    |
  12. +----+-------+-------+
  13. |  2 | rt2c2 | rt2c3 |
  14. +----+-------+-------+
  16. greatsql> SELECT * FROM t2;
  17. +----+-------+-------+
  18. | c1 | c2    | c3    |
  19. +----+-------+-------+
  20. |  2 | rt2c2 | rt2c3 |
  21. +----+-------+-------+
    1. 示例3：更新值源自子查询
  1. -- 对t1, t2表重新初始化
  2. greatsql> TRUNCATE TABLE t1;
  3. greatsql> INSERT INTO t1 VALUES (1, 'rt1c21', 'rt1c31');
  5. greatsql> TRUNCATE TABLE t2;
  6. greatsql> INSERT INTO t2 VALUES (1, 'rt2c21', 'rt2c31'),
  7. (2, 'rt2c22', 'rt2c32'),
  8. (3, 'rt2c23', 'rt2c33');
  10. greatsql> SET sql_mode = ORACLE;
  12. -- 相比用法2，少了最后t1表上的WHERE条件
  13. greatsql> UPDATE t1 SET(c2, c3) = (SELECT c2, c3 FROM t2 WHERE c1 = 3);
  14. Query OK, 3 rows affected (0.00 sec)
  15. Rows matched: 3  Changed: 3  Warnings: 0
  17. greatsql> SELECT * FROM t1;
  18. +----+--------+--------+
  19. | c1 | c2     | c3     |
  20. +----+--------+--------+
  21. |  1 | rt2c23 | rt2c33 |
  22. |  2 | rt2c23 | rt2c33 |
  23. |  3 | rt2c23 | rt2c33 |
  24. +----+--------+--------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx