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