我有一个MySQL问题
我想将下一个查询的结果存入一个var中,并使用它更新到触发器中,我正在尝试将名称逗号分隔
查询返回多一行
SELECT t.naam
FROM trefwoorden t
INNER JOIN organisaties_has_trefwoorden AS o ON (t.id_trefwoorden = o.id_trefwoorden)
WHERE o.id_organisaties = NEW.id_organisaties;
这是触发因素
CREATE TRIGGER updCheck_After AFTER
UPDATE ON organisaties_has_trefwoorden
FOR EACH row
UPDATE organisaties o
SET o.trefwoorden_flat =
(
SELECT t.naam
FROM trefwoorden t
INNER JOIN organisaties_has_trefwoorden AS o ON (t.id_trefwoorden = o.id_trefwoorden)
WHERE o.id_organisaties = NEW.id_organisaties;
)
WHERE o.id_organisaties = NEW.id_organisaties
问题是有可能选择更多行作为1列逗号分隔
还是有其他解决方案？
有人建议吗？
最佳答案
UPDATE organisaties o
SET o.trefwoorden_flat =
(
SELECT GROUP_CONCAT(t.naam SEPARATOR ',')
FROM trefwoorden t
INNER JOIN
organisaties_has_trefwoorden AS o
ON t.id_trefwoorden = o.id_trefwoorden
WHERE o.id_organisaties = NEW.id_organisaties
)
WHERE o.id_organisaties = NEW.id_organisaties