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