更新语法分 单表 和 多表
# 单表操作 UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] value: {expr | DEFAULT} assignment: col_name = value assignment_list: assignment [, assignment] ... # 多表操作 UPDATE [LOW_PRIORITY] [IGNORE] table_references SET assignment_list [WHERE where_condition]
table_references: escaped_table_reference [, escaped_table_reference] ... escaped_table_reference: { table_reference | { OJ table_reference } } table_reference: { table_factor | joined_table } table_factor: { tbl_name [PARTITION (partition_names)] [[AS] alias] [index_hint_list] | [LATERAL] table_subquery [AS] alias [(col_list)] | ( table_references ) } joined_table: { table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification] | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor } join_specification: { ON search_condition | USING (join_column_list) } join_column_list: column_name [, column_name] ... index_hint_list: index_hint [, index_hint] ... index_hint: { USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) | {IGNORE|FORCE} {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) } index_list: index_name [, index_name] ...
# col1 最后的值是 在执行表示式时的当前值 + 1,即 原来的值 + 1 UPDATE t1 SET col1 = col1 + 1; # col1 的值 同上, col2 的值 是 col1 的当前值,这时col1 已经 +1 了,所以 col2 最后和 col1 值相等 UPDATE t1 SET col1 = col1 + 1, col2 = col1;
# id 是唯一的数字列,若id有多个从小到大的连续值(1,2,3),此时更新会报错。因为默认会按顺序更新id:1+1 =》2 就会和已存在的 2 冲突 UPDATE t SET id = id + 1; # 使用 order by id desc 按 id 倒序排列。先更新最大的值,就不会出新 重复值 错误 UPDATE t SET id = id + 1 ORDER BY id DESC;
# 在 table_references 中使用了 内连接 UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
# 表结构 CREATE TABLE items ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00, retail DECIMAL(6,2) NOT NULL DEFAULT 0.00, quantity BIGINT NOT NULL DEFAULT 0 ); # 更新 retail,直接子查询,报错 UPDATE items SET retail = retail * 0.9 WHERE id IN (SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity > 100); ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause # 将子查询作为导出表(临时表),再用多表更新的方式 UPDATE items, (SELECT id FROM items WHERE id IN (SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity < 100)) AS discounted SET items.retail = items.retail * 0.9 WHERE items.id = discounted.id; # 或者 UPDATE items, (SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity < 100) AS discounted SET items.retail = items.retail * 0.9 WHERE items.id = discounted.id; # 或者 UPDATE items, (SELECT id, retail / wholesale AS markup, quantity FROM items) AS discounted SET items.retail = items.retail * 0.9 WHERE discounted.markup >= 1.3 AND discounted.quantity < 100 AND items.id = discounted.id;
https://dev.mysql.com/doc/refman/8.0/en/update.html
表达式: https://dev.mysql.com/doc/refman/8.0/en/expressions.html
函数 和 操作符: https://dev.mysql.com/doc/refman/8.0/en/functions.html
自动生成列:https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html