MySQL (Wordpress) - 更新时的内部连接

Question

我正在尝试将可以找到重复 post_title 的任何"new"记录的字段后状态更新为“草稿”。以下选择查询按预期工作并显示我要更新的记录 -

select a.* FROM wp_posts AS a INNER JOIN (SELECT Greater1.post_title, Titles.ID, 
Greater1.MinID FROM (SELECT post_title, MIN(ID) AS 'MinID', MAX(ID) AS 'MaxID' FROM 
wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY post_title 
HAVING COUNT(post_title)>1) AS Greater1 LEFT JOIN (SELECT post_title, ID FROM 
wp_posts) AS Titles ON Greater1.post_title = Titles.post_title WHERE ID > MinID) AS 
b ON a.ID = b.ID WHERE a.ID = b.ID

但是下面的更新查询给出了语法错误。有帮助吗？

update a.wp_posts set a.post_status='draft' FROM wp_posts AS a INNER JOIN (SELECT 
Greater1.post_title, Titles.ID, Greater1.MinID FROM (SELECT post_title, MIN(ID) AS 
'MinID', MAX(ID) AS 'MaxID' FROM wp_posts WHERE post_type = 'post' AND post_status = 
'publish' GROUP BY post_title HAVING COUNT(post_title)>1) AS Greater1 LEFT JOIN 
(SELECT post_title, ID FROM wp_posts) AS Titles ON Greater1.post_title = 
Titles.post_title WHERE ID > MinID) AS b ON a.ID = b.ID WHERE a.ID = b.ID

非常感谢。

Answer 1

您使用的语法是针对MSSQL，这是针对MySQL

UPDATE wp_posts AS a
        INNER JOIN 
        (
            SELECT Greater1.post_title,
                    Titles.ID,
                    Greater1.MinID
            FROM 
            (
                    SELECT post_title,
                            MIN(ID) AS 'MinID',
                            MAX(ID) AS 'MaxID'
                    FROM wp_posts
                    WHERE post_type = 'post'
                            AND post_status = 'publish'
                    GROUP BY post_title
                    HAVING COUNT(post_title) > 1
            ) AS Greater1
                LEFT JOIN 
                (
                    SELECT post_title,
                            ID
                    FROM wp_posts
                ) AS Titles
                    ON Greater1.post_title = Titles.post_title
            WHERE ID > MinID
        ) AS b ON a.ID = b.ID
SET a.post_status = 'draft'
WHERE a.ID = b.ID

• > UPDATE with JOIN syntax