在数据表查询中批量更新

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}来自下面的Join:

我想在credit列中填写相同的创建者值，该怎么做呢

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;

Answer 2

错误在SET中:这里必须输入列名，而不是条件

update mp SET columnname where credit = somecondition