UPDATE

语法描述

UPDATE 用于修改表中的现有记录。

语法结构

单表语法结构

  1. UPDATE table_reference
  2.     SET assignment_list
  3.     [WHERE where_condition]
  4.     [ORDER BY ...]
  5.     [LIMIT row_count]

参数释义

  • UPDATE 将新值更新到指定表中现有行的列中。
  • SET 从句指出要修改哪些列以及它们应该被赋予的值。每个值可以作为表达式给出，或者通过 DEFAULT 明确将列设置为默认值。
  • WHERE 从句，用于指定用于标识要更新哪些行的条件。若无 WHERE 从句，则更新所有行。
  • ORDER BY 从句，指按照指定的顺序更新行。
  • LIMIT 从句用于限制可更新的行数。

示例

  • 单表示例
  1. CREATE TABLE t1 (a bigint(3), b bigint(5) primary key);
  2. insert INTO t1 VALUES (1,1),(1,2);
  3. update t1 set a=2 where a=1 limit 1;
  5. mysql> select * from t1;
  6. +------+------+
  7. | a    | b    |
  8. +------+------+
  9. |    2 |    1 |
  10. |    1 |    2 |
  11. +------+------+
  • 多表示例
  1. drop table if exists t1;
  2. create table t1 (a int);
  3. insert into t1 values(1), (2), (4);
  4. drop table if exists t2;
  5. create table t2 (b int);
  6. insert into t2 values(1), (2), (3);
  7. update t1, t2 set a = 1, b =2;
  9. mysql> select * from t1;
  10. +------+
  11. | a    |
  12. +------+
  13. |    1 |
  14. |    1 |
  15. |    1 |
  16. +------+
  18. update t1, t2 set a = null, b =null;
  20. mysql> select * from t2;
  21. +------+
  22. | b    |
  23. +------+
  24. | NULL |
  25. | NULL |
  26. | NULL |
  27. +------+
  28. mysql> select * from t1;
  29. +------+
  30. | a    |
  31. +------+
  32. | NULL |
  33. | NULL |
  34. | NULL |
  35. +------+

支持多表 JOIN 语句。

  1. drop table if exists t1;
  2. drop table if exists t2;
  3. create table t1 (a int, b int, c int);
  4. insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
  5. create table t2 (a int, b int, c int);
  6. insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
  7. update t1 join t2 on t1.a = t2.a set t1.b = 222, t1.c = 333, t2.b = 222, t2.c = 333;
  9. mysql> select * from t1;
  10. +------+------+------+
  11. | a    | b    | c    |
  12. +------+------+------+
  13. |    1 |  222 |  333 |
  14. |    4 |  222 |  333 |
  15. |    7 |  222 |  333 |
  16. +------+------+------+
  18. mysql> with t11 as (select * from (select * from t1) as t22) update t11 join t2 on t11.a = t2.a set t2.b = 666;
  20. mysql> select * from t2;
  21. +------+------+------+
  22. | a    | b    | c    |
  23. +------+------+------+
  24. |    1 |  666 |  333 |
  25. |    4 |  666 |  333 |
  26. |    7 |  666 |  333 |
  27. +------+------+------+
  28. 3 rows in set (0.00 sec)