此SQL DELETE FROM语法有什么问题? [英] What is wrong with this SQL DELETE FROM syntax?

问题描述

我正在尝试删除96k条记录.

I'm trying to delete 96k records.

此查询有效，返回了91k条记录:

This query worked returning 91k records:

SELECT *  
   FROM xoops_bb_posts_text t  
   WHERE not exists (
       select post_id 
           from xoops_bb_posts p 
           WHERE p.post_id = t.post_id 
   );  

当我尝试删除那些记录时，出现语法错误，但是没有看到.

when I tried to delete those records I got a syntax error, but I don't see it.

DELETE FROM xoops_bb_posts_text t  
WHERE not exists (
    select post_id 
       from xoops_bb_posts p 
    WHERE p.post_id = t.post_id 
);  

错误在哪里?

错误

SQL查询:文档

DELETE FROM xoops_bb_posts_text t 
   WHERE NOT EXISTS (  
      SELECT post_id  
         FROM xoops_bb_posts p  
         WHERE p.post_id = t.post_id  
)  

MySQL说:文档

#1064-您的SQL语法有错误；检查手册 对应于您的MySQL服务器版本以使用正确的语法 附近'不存在(从xoops_bb_posts p中选择post_id p.post_id = t.post_'，位于第2行

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE not exists (select post_id from xoops_bb_posts p WHERE p.post_id = t.post_' at line 2

推荐答案

对我来说，通过使用带有outer join的delete语句并查找不匹配的行，可以更轻松地解决此问题.像这样:

To me, this problem is more easily solved by using a delete statement with a an outer join and looking for the rows that had no match. Something like this:

delete t from xoops_bb_posts_text as t
left outer join xoops_bb_posts as p
on p.post_id = t.post_id
where p.post_id is null;

或仅更改您的查询:

DELETE t
FROM xoops_bb_posts_text t  
WHERE not exists 
      (select post_id from xoops_bb_posts p WHERE p.post_id = t.post_id );

这篇关于此SQL DELETE FROM语法有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！