MySQL语句在Update中使用group_concat

Question

我想将一些数据从多行复制到一行。当我使用 Select 时，一切都按我想要的方式显示，但我无法将其转换为 Update 查询。请帮我找出问题选择和更新是:

SELECT CONCAT( P.POST_CONTENT, GROUP_CONCAT(M.meta_value ORDER BY M.meta_id SEPARATOR 'CHAR(10)'))
FROM posts P
INNER JOIN postmeta M ON P.ID=M.post_id
WHERE M.meta_key = 'wpcf-obrazek'
GROUP BY P.ID

和

    UPDATE P, M
    SET P.post_excerpt = CONCAT( P.POST_CONTENT, GROUP_CONCAT(M.meta_value ORDER BY M.meta_id SEPARATOR 'CHAR(10)'))
    FROM posts P
    INNER JOIN postmeta M ON P.ID=M.post_id
    WHERE M.meta_key = 'wpcf-obrazek'
    GROUP BY P.ID

错误是:

#1064 - Something is wrong in your syntax obok 'FROM posts P
    INNER JOIN postmeta M ON P.ID=M.post_id
    WHERE M.meta_key ' w linii 3

Answer 1

如果您将派生表与预处理数据连接起来，更新应该会起作用，请尝试:

UPDATE P
INNER JOIN (
             SELECT P.ID, CONCAT( P.POST_CONTENT, GROUP_CONCAT(M.meta_value ORDER BY M.meta_id SEPARATOR 'CHAR(10)')) AS data
             FROM posts P
               INNER JOIN postmeta M ON P.ID=M.post_id
             WHERE M.meta_key = 'wpcf-obrazek'
             GROUP BY P.ID
           ) AS excerpt_data ON P.ID = excerpt_data.ID
SET P.post_excerpt = excerpt_data.data;