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

# LOW_PRIORITY
UPDATE LOW_PRIORITY employees SET department_id=2;

Output

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

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

Output

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

# 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

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

# IGNORE
UPDATE IGNORE employees SET department_id=3;

Output

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