tags:

views:

58

answers:

2

Hi all, 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.

+1  A: 

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)
Larry Lustig
+1: Nice to see you again, Larry.
OMG Ponies
Yes, you're right, I do! Whoops, sorry.
Doctor Trout
Thanks, Ponies. I've been busy, but hope to spend more time here this fall.
Larry Lustig
+2  A: 

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 table. 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.

OMG Ponies
Ah yes, wonderful! Thank you.
Doctor Trout