mysql update常见实例

在MySQL中使用update语句的时候，Where条件或者值都可以使用子查询，比如：

UPDATE table_a SET name=(SELECT name FROM table_b WHERE id=1) 
 WHERE id IN (SELECT id FROM table_c WHERE age > 24);

但是如果子查询和更新的表是同一个表的话，MySQL会报如下的错误：中涉及到的子查询要格外注意

Error Code : 1093
You can't specify target table 'table_a' for update in FROM clause

 

我们平时更新数据时候常见的就有如下几种：

1、最简单的语句如下：

1、UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';
2、UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

2、将同一个表中的一个字段的值复制给另一个字段，常见操作如下：

1、UPDATE t_user  SET signed_time = create_time 
2、UPDATE table_a SET A=(SELECT B FROM (SELECT * FROM table_a) b WHERE b.id = table_a.id)
3、update tbl_user_info_copy as aa, tbl_user_info_copy as bb  
set aa.userId = bb.id 
WHERE aa.id = bb.id;

3、将同一个表中两个类型一样的字段的值互换（有待完善）

UPDATE t_user u1, t_user u2 
SET u1.signed_time = u2.create_time,  
    u2.create_time = u1.signed_time 

4、一个表的某个字段赋值给另一个表的某个字段

UPDATE tbl_a, tbl_b
SET tbl_a.nick = tbl_b.userName
WHERE tbl_b.userId=tbl_b.id;

UPDATE tbl_a
inner JOIN tbl_b
on tbl_a.userId=tbl_b.id
SET tbl_a.nick=tbl_a.userName;

5、多表关联Update

update table_1 set score = score + 5 where uid in (select uid from table_2 where sid = 10);

其实update也可以用到left join、inner join来进行关联，可能执行效率更高，把上面的sql替换成join的方式如下：

update table_1 t1 inner join table_2 t2 on t1.uid = t2.uid set score = score + 5 where t2.sid = 10;

 6、同时更新多个表

UPDATE tbl_a,tbl_b
SET tbl_a.nick=tbl_b.userName,tbl_b.nick = tbl_b.nick + "11"
WHERE tbl_a.userId = tbl_b.id;

或者使用join

UPDATE tbl_a
INNER JOIN
tbl_b
ON tbl_a.userId=tbl_b.id
SET tbl_a.nick=tbl_b.userName,tbl_b.nick=tbl_b.nick+ "11";

下面举例如下：

假定目前有两张表goods和goods_price表，前者是保存商品的具体信息，后者是保存商品的价格，具体的表结构如下：

create table goods (
`id` int unsigned primary key auto_increment,
`goods_name` varchar(30) not null default '',
`deleted_at` int unsigned default null
)engine innodb charset utf8;
create table goods_price (
`goods_id` int unsigned not null,
`price` decimal(8,2) not null default '0.00'
)engine innodb charset utf8;
insert into goods (id,goods_name) values (1,'商品1'),(2,'商品2'),(3,'商品3'),(4,'商品4'),(5,'商品5');
insert into goods_price values (1,'5.44'),(2,'3.22'),(3,'5.55'),(4,'0.00'),(5,'4.54');

在update时使用逗号分割更新

将未删除的商品的价格*0.5，具体SQL语句如下：

UPDATE goods as g , goods_price as p SET p.price = p.price*0.5 WHERE p.goods_id = g.id AND g.deleted_at is null;

使用inner join更新数据

UPDATE goods g INNER JOIN goods_price p ON g.id=p.goods_id SET p.price=p.price*0.5 where g.deleted_at is null;

更新多个表

上面的更新语句使用另一个表的条件，更新一张表，也可以更新多个表。具体SQL语句如下：

UPDATE goods g INNER JOIN goods_price p on g.id=p.goods_id set p.price=p.price*0.5,g.deleted_at=unix_timestamp(now()) where g.is_deleted_at is null;

 参考：

1、https://blog.csdn.net/fansunion/article/details/52130365

2、https://www.kancloud.cn/curder/mysql/355258