mysql数据库语法求助

sxfxtf

2个表分别是cailanzi和category
cailanzi 数据如下


category表数据如下


想实现用update语法将 cailanzi.category的字段全部更新成category.father字段  匹配条件是where cailanzi.commodity_name = category.item_name

又要麻烦各位大佬们指点了,拜谢

entonghou

update cailanzi a,category b set a.category = b.father where a.commodity_name = b.item_name;

Dream_Peng

update cailanzi set category = (select father_name from category where cailanzi.commodity_name = category.item_name)

branch518

update cailanzi a,category b set a.category=b.father_name where a.commodity_name =b.item_name

jxyu1013

update cailanzi r left join (select r.commodity_name,i.father_name
from category i INNER JOIN cailanzi r
where i.item_name=r.commodity_name) re on r.commodity_name=re.commodity_name
set r.category=re.father_name


你试试吧，没细看，大致sql

kill-9

update cailanzi
set category= (select father from cailanzi,category  where cailanzi.commodity_name = category.item_name)

xiaomache

题外话，为啥没有cailanzi没有category表的ID关联，是设计上用不到吗？

sxfxtf

感谢 上面流程的大哥们的解答,我自己试试看

sxfxtf

xiaomache 发表于 2022-10-24 15:52
题外话，为啥没有cailanzi没有category表的ID关联，是设计上用不到吗？

因为数据是我爬取下来的,大约有30多万条. 插入的时候就没做关联匹配.怕影响写入的速度

jylx

UPDATE cailanzi
SET cailanzi.category=category.father_name
FROM cailanzi,category
WHERE cailanzi.commodity_name=category.item_name;