时间戳

首页 > 解决方案 > 使用 INNER JOIN 更新 sql 数据

mysql - 使用 INNER JOIN 更新 sql 数据

问题描述

我想将 wordpress 数据库中的 post_status 从草稿更改为垃圾箱以获取具有相同 post_title 的重复帖子，并且我使用此 sql 查询

      UPDATE a.post_status SET `post_status` = 'trash'
        FROM wp_posts AS a
         INNER JOIN (
           SELECT post_title, MIN( id ) AS min_id
           FROM wp_posts
           WHERE post_type = 'post'
           AND post_status = 'draft'
           GROUP BY post_title
           HAVING COUNT( * ) > 1
         ) AS b ON b.post_title = a.post_title
        AND b.min_id <> a.id
        AND a.post_type = 'post'
        AND a.post_status = 'draft'

执行查询后显示错误

  Error Code: 1064
    Erreur de syntaxe près de 'FROM wp_posts AS a
     INNER JOIN (
       SELECT post_title, MIN( id ) AS min_id
      ' à la ligne 2

此查询的正确语法是什么？

标签： mysqlsqldatabasewordpresssyntax

解决方案

MySQL 中的正确语法不使用FROM：

UPDATE post_status ps INNER JOIN
       (SELECT post_title, MIN( id ) AS min_id
        FROM wp_posts
        WHERE post_type = 'post' AND
              post_status = 'draft'
        GROUP BY post_title
        HAVING COUNT( * ) > 1
       ) p
       ON p.post_title = ps.post_title AND
          p.min_id <> ps.id AND
          ps.post_type = 'post' AND
          ps.post_status = 'draft'
 SET ps.post_status = 'trash';

请注意，我还将表格别名从无意义的字母更改为更有意义的缩写。

推荐阅读