首先,在mysql workbench内执行update时,缺省是使用安全更新模式的,如果在update sql内的where没有指定id等主键条件,会告警并推出执行。你可以关闭安全模式,执行如下语句即可。
SET SQL_SAFE_UPDATES=0;
这是mysql中的update 语句的样式:
# Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
# Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
再说一下update中的限制:
1. update 时,更新的表不能在set和where中用于子查询
2. update 时,可以对多个表进行更新 如:
update table_a a,table_b b set a.b_id=b.id, b.a_id=a.id;
3. update 后面可以做任意的查询,这个作用等同于from
如下展示一个网友的例子:
mysql> create table IF NOT EXISTS tb (Name varchar(10),Time1 datetime,Time2 date
time,Time3 varchar(8),Time4 varchar(8) );
Query OK, 0 rows affected (0.03 sec)
插入一些数据后,进行带有子查询的update操作:
// 这是mysql中update使用子查询处理
mysql> update tb a,
-> (select SEC_TO_TIME(sum(TIME_TO_SEC(time3))) col,max(time1) time,name
-> from tb group by DATE_FORMAT(time1, '%Y-%m-%d'))b
-> set time4=b.col
-> where a.name=b.name and a.time1=b.time;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5 Changed: 4 Warnings: 0
又比如:
//
update tch, (
SELECT (ta.base_pay-80.0) as pay,ta.tch_id
FROM t_a ta
WHERE ta.STATUS = "INTERVIEW"
AND ta.result = "PASS"
AND ta.base_pay < 19
AND ta.tch_id in(
SELECT DISTINCT
tb.tch_id
FROM
t_a tb JOIN tch t2 ON tb.tch_id = t2.id
WHERE tb.STATUS = "INTERVIEW"
AND tb.result = "PASS"
AND tb.base_pay < 190
AND t2.extra_class_salary IS NULL
)
) tmps
set tch.extra_class_salary = tmps.pay
where tch.id = tmps.tch_id;