CODEWITHSUNDEEP
phpsqlsqlite
Simon
Simon

Reputation: 797

Delete all rows that aren't in another table in 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.

Upvotes: 1

Views: 2958

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332681

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.

Upvotes: 5

Larry Lustig
Larry Lustig

Reputation: 51000

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)

Upvotes: 1

Related Questions