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

Question

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.

Answer 1

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.