mysql - datatasbe 查询中的批量更新

Question

我遇到一个问题，我正在尝试更新批量更新以替换另一列中的一列的值。

这是我从以下查询生成的数据

 ID     CodeID  credit   Image      creator   
47774   5635    none    5635.jpg    Freshy
47790   5643    none    5643.jpg    Fresh
47792   5643    none    5643AB.jpg  Fresh
47793   5643    none    5643FF.jpg  Fresh
47795   5643    none    56431.jpg   Fresh
47796   5643    none    56434B.jpg  Fresh

最后一列 {Creator} 来自以下联接:

我想在信用栏中填写相同的创建者值，该怎么做

select mp.id, codeid, credit, image, creator 
from mp inner join mm on mm.id = mp.codeid 
where credit = 'none' 

尝试了这样的更新

update mp SET credit = (select creator 
from mp inner join mm on mm.id = mp.modid 
where credit = 'none') 

最终出现错误:

[Err] 1093 - You can't specify target table 'mods_pics_copy' for update in FROM clause

可能有某种方法可以做到这一点

Answer 1

使用此语法

UPDATE mp
JOIN mm ON mm.id = mp.codeid AND credit = 'none'
SET mp.credit = mm.creator;