【无标题】

用子查询的结果更新表字段

有的时候我们在做数据库设计的时候，早期设计的字段存储中文名称的拼接字符串。
后期，发现这样的设计很不方便，我们需要把这个字段改成存储id拼接的字符串。
嗯，这时候要处理数据了，需要把facility这个字段的数据更改成 facility表查询出来的id字段拼接字符串。如下是我实操的代码留个纪念。

查询优化版
select s.facility,c.facility_id ,s.id from room_type s
INNER JOIN
(
select a.id,a.facility,case when GROUP_CONCAT(b.id) is null then ‘’ else GROUP_CONCAT(b.id) end as facility_id ,b.id as b_id from room_type as a
left join facility b on a.store_id = b.store_id and find_in_set(b.name,a.facility)
group by a.id
) c
ON s.id = c.id

//成功版
update room_type s
INNER JOIN
(
select a.id,a.facility,case when GROUP_CONCAT(b.id) is null then ‘’ else GROUP_CONCAT(b.id) end as facility_id ,b.id as b_id from room_type as a
left join facility b on a.store_id = b.store_id and find_in_set(b.name,a.facility)
group by a.id
) c
ON s.id = c.id
set s.facility = c.facility_id