Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ MySQL Syntax Migrating/ Data Manipulation Language (DML)/ UPDATE
Updated on 2024-12-09 GMT+08:00
View PDF
Share

UPDATE

In MySQL, UPDATE allows the following keywords: LOW_PRIORITY, ORDER BY, LIMIT, and IGNORE.

LOW_PRIORITY

With the LOW_PRIORITY modifier, execution of UPDATE is delayed.

Input

1
2
 
# LOW_PRIORITY
UPDATE LOW_PRIORITY employees SET department_id=2;

Output

1
2
 
-- LOW_PRIORITY
UPDATE "public"."employees" SET "department_id" = 2;

ORDER BY

In MySQL, if an UPDATE statement includes an ORDER BY clause, the rows will be updated in the order specified by the clause.

Input

1
2
 
# ORDER BY
UPDATE employees SET department_id=department_id+1 ORDER BY id;

Output

1
2
 
-- ORDER BY
UPDATE "public"."employees" SET "department_id" = department_id+1;

LIMIT

UPDATE LIMIT syntax can be used to limit the scope. A clause is a limit on row matching. As long as the rows that satisfy the clause are found, the statements will stop, regardless of whether they have actually changed.

Input

 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 ;

Output

 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

With the IGNORE modifier, the UPDATE statement does not abort even if errors occur during execution.

Input

1
2
 
# IGNORE
UPDATE IGNORE employees SET department_id=3;

Output

1
2
 
-- IGNORE
UPDATE "public"."employees" SET "department_id" = 3;