文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ MySQL语法迁移/ 数据操作语句（DML）/ UPDATE
更新时间：2024-11-15 GMT+08:00
查看PDF
分享

UPDATE

MySQL的UPDATE操作形式包括：LOW_PRIORITY、ORDER BY、LIMIT 、IGNORE。

LOW_PRIORITY

MySQL UPDATE语句如果使用LOW_PRIORITY修饰符，则执行UPDATE延迟。

输入示例

1
2
 
#测试 LOW_PRIORITY 语法点
UPDATE LOW_PRIORITY employees SET department_id=2;

输出示例

1
2
 
--测试 LOW_PRIORITY 语法点
UPDATE "public"."employees" SET "department_id" = 2;

ORDER BY

如果一个MySQL UPDATE语句包含一个 ORDER BY子句，则这些行将按照该子句指定的顺序更新。

输入示例

1
2
 
# 测试 ORDER BY 语法点
UPDATE employees SET department_id=department_id+1 ORDER BY id;

输出示例

1
2
 
-- 测试 ORDER BY 语法点
UPDATE "public"."employees" SET "department_id" = department_id+1;

LIMIT

UPDATE LIMIT语法可以用来限制的范围。一个子句是一个行匹配的限制。只要发现满足该子句的行， 语句就会停下来 ，不管它们是否真的发生了变化。

输入示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
#单独测试 LIMIT 语法点
UPDATE  employees SET department_id=department_id+1   LIMIT 3 ;
UPDATE  employees SET department_id=department_id+1   LIMIT 3 , 10 ;

#测试 LIMIT + OFFSET 语法点
UPDATE  employees SET department_id=department_id+1   LIMIT 3   OFFSET 2;

#测试 LIMIT + ORDER BY 语法点搭配使用
UPDATE  employees SET department_id=department_id+1 ORDER BY fname  LIMIT 3 ;

#测试 LIMIT + WHERE + ORDER BY 语法点搭配使用
UPDATE  employees SET department_id=department_id+1 WHERE id<5 ORDER BY  fname  LIMIT 3 ;

#测试 LIMIT + WHERE + ORDER BY + OFFSET 语法点搭配使用
UPDATE  employees SET department_id=department_id+1 WHERE id<5 ORDER BY  fname  LIMIT 3 OFFSET 2 ;

输出示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
--单独测试 LIMIT 语法点
UPDATE "public"."employees" SET "department_id" = department_id+1;
UPDATE "public"."employees" SET "department_id" = department_id+1;

--测试 LIMIT + OFFSET 语法点
UPDATE "public"."employees" SET "department_id" = department_id+1;

--测试 LIMIT + ORDER BY 语法点搭配使用
UPDATE "public"."employees" SET "department_id" = department_id+1;

--测试 LIMIT + WHERE + ORDER BY 语法点搭配使用
UPDATE "public"."employees" SET "department_id" = department_id+1 WHERE id<5;

--测试 LIMIT + WHERE + ORDER BY + OFFSET 语法点搭配使用
UPDATE "public"."employees" SET "department_id" = department_id+1 WHERE id<5;

IGNORE

MySQL UPDATE语句如果使用IGNORE修饰符，即使更新期间发生错误，UPDATE语句也不会中止。

输入示例

1
2
 
#测试 IGNORE 语法点
UPDATE IGNORE employees SET department_id=3;

输出示例

1
2
 
--测试 IGNORE 语法点
UPDATE "public"."employees" SET "department_id" = 3;