我有一个类似的表
title ACCNR ID
Hello1 1 1
Hello1 1 2
Hello1 3
Hello1 4
Hello2 3 5
Hello2 3 6
Hello2 7
Hello2 8
现在,我想为相同的标题填写缺失的ACCNR。
编辑:结果应该是:
title ACCNR ID
Hello1 1 1
Hello1 1 2
Hello1 **1** 3
Hello1 **1** 4
Hello2 3 5
Hello2 3 6
Hello2 **3** 7
Hello2 **3** 8
我试过这个:
update refs join refs as p set refs.accnr =
IF (
SELECT GROUP_CONCAT(DISTINCT refs.accnr order by accnr))
from refs
where refs.id = p.id and refs.accnr <> '' GROUP BY refs.title
IS NULL ' ',c1) where p.id=refs.id;
原因是,我得到这个错误
[Err] 1048 - Column 'custom_1' cannot be null
通过第一次尝试:
update refs join refs as p set refs.custom_1 =
(
SELECT GROUP_CONCAT(DISTINCT refs.accnr order by accnr)
from refs
where refs.id = p.id and refs.accnr <> '' GROUP BY refs.title
) where p.id=refs.id;
是什么让我错了?如果有任何建议,我将不胜感激。
EDIT:这种方法使用第二个表:
drop table if EXISTS accnr;
create table accnr (SELECT GROUP_CONCAT(DISTINCT refs.accnr order
by accnr) as accnr, concat(',',GROUP_CONCAT(refs.id order by
refs.id),',') as idlist from refs
where refs.accnr <> '' GROUP BY refs.titel,);
update refs join accnr set custom_1 = accnr.accnr where refs.id like concat('%,',refs.id,',%');
如果我理解正确,您只需要用与第一行相同的值更新列accnr
。
你可以这样更新你的表格:
Update your_table b Join
(Select title, accnr from your_table where accnr is not null group by title) a
on b.title = a.title set b.accnr =a.accnr;