update同一张表的例子
场景:
业务中台--的bd_area表需要把pk_parent(父id)字段更新
(select b.id,substr(b.area_code,1,2) as bcode,substr(b.tree_code,1,3) as btcode from bd_area b where LENGTH(b.tree_code)=3) m
on substr(a.area_code,1,2)=m.bcode and substr(a.tree_code,1,3)=m.btcode
(select b.id,substr(b.area_code,1,4) as bcode,substr(b.tree_code,1,6) as btcode from bd_area b where LENGTH(b.tree_code)=6) m
on substr(a.area_code,1,4)=m.bcode and substr(a.tree_code,1,6)=m.btcode
1.最开始的思路是根据areacode和treecode作为关联更新批量更新sql
set a.pk_parent=(select b.id from bd_area b where LENGTH(b.tree_code)=3
and substr(a.area_code,1,2)=substr(b.area_code,1,2) and substr(a.tree_code,1,3)=substr(b.tree_code,1,3)
报错:You can't specify target table 'table name' for update in FROM clause
(不能先将select出表中的某些值,再update这个表(在同一语句中)
方案一没作用(delete数据可能会有作用,但是我的update语句不好使)
https://blog.****.net/qq_37709240/article/details/80049744
update 表1 a1 inner join (select 字段1,字段2 from 表1 where 条件) a2 on 条件
哇,这个思路厉害了啊 先造出来一个虚拟表,然后通过更新虚拟表的方式去实现具体的更新;
(select b.id,substr(b.area_code,1,2) as bcode,substr(b.tree_code,1,3) as btcode from bd_area b where LENGTH(b.tree_code)=3)
on substr(a.area_code,1,2)=b.bcode and substr(a.tree_code,1,3)=b.btcode
报错:Every derived table must have its own alias
(select b.id,substr(b.area_code,1,2) as bcode,substr(b.tree_code,1,3) as btcode from bd_area b where LENGTH(b.tree_code)=3) m
on substr(a.area_code,1,2)=m.bcode and substr(a.tree_code,1,3)=m.btcode
1.update同一张表的其中一种解决思路是(适应某些场景,自己去品)
update 表1 a1 inner join (select 字段1,字段2 from 表1 where 条件) a2 on 条件
先造出来一个虚拟表,然后通过更新虚拟表的方式去实现具体的更新;
2.错误:Every derived table must have its own alias
在做多表查询,或者查询的时候产生新的表的时候会出现这个错误:Every derived table must have its own alias(每一个派生出来的表都必须有一个自己的别名)。
参考:https://blog.****.net/qq_32863631/article/details/83024322
3.错误:You can't specify target table 'table name' for update in FROM clause