drlnsli18864734
2010-10-06 19:56
浏览 155

删除SQLite中不在另一个表中的所有行

I have two tables (A and B).

I want to delete all the rows in Table B where B.1 isn't in Table A.2.

So I wrote this formula in sqlite:

DELETE FROM B 
WHERE 1 
IN 
 (SELECT * 
  FROM B 
  LEFT JOIN A 
  ON A.1=B.2 
  WHERE A.1 
  IS NULL)

But this returns this error:

only a single result allowed for a SELECT that is part of an expression

Could anyone give me a hand?

Thanks.

图片转代码服务由CSDN问答提供 功能建议

我有两个表(A和B)。

我想要 删除表B中B.1不在表A.2中的所有行。

所以我在sqlite中写了这个公式:

   DELETE FROM B 
WHERE 1 
IN 
(SELECT * 
 FROM B 
 LEFT JOIN A 
 ON A.1 = B.2 
 WHERE A.1 
 IS NULL)
   
 
 

但是这会返回以下错误:

 只允许一个结果允许SELECT作为表达式的一部分
 <  / code>  
 
 

有人可以帮忙吗?

谢谢。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doujin4031 2010-10-06 20:03
    已采纳

    The issue for your example query is that an IN clause can not be used in conjunction with SELECT * when the SELECT * returns more than one column. You need to specify the column...

    NOT IN

    DELETE FROM B
     WHERE B.2 NOT IN (SELECT A.1
                         FROM A)
    

    NOT EXISTS

    DELETE FROM B
     WHERE NOT EXISTS (SELECT NULL
                         FROM A
                        WHERE A.1 = B.2)
    

    SQLite doesn't support JOINs in DELETE statements, but you could also use:

    DELETE FROM B
     WHERE B.2 IN (SELECT B.2
                     FROM B
                LEFT JOIN A ON A.1 = B.2
                    WHERE A.1 IS NULL)
    

    Conclusion:

    I don't have any performance statistics for SQLite, but the NOT EXISTS would be my choice because it returns true on the first time it's satisfied--very good for dealing with duplicates.

    点赞 评论
  • dsfs64664 2010-10-06 19:59

    I think you have a typo somewhere in your question (maybe table B.2?), but I think what you want is:

    DELETE FROM B WHERE NOT EXISTS (SELECT * FROM A WHERE A.1 = B.2)
    
    点赞 评论

相关推荐 更多相似问题