[RESOLVED] Sql 'in'

[Doogle]

Playing around with some SQL to DELETE a Record from a Table if (a) it exists and (b) and entry related to that record doesn't exist in another Table.

Say I have Table1 with columns: ID and Name, and Table2 with columns: ID1 and Name, the records are related by the Name columns.

I could use 2 SQL statement, one to SELECT the Name from Table1 WHERE the ID = the ID I'm looking for, then, if anything was returned, SELECT FROM Table2 where Name= the Name returned from the first SELECT. If nothing is returned then DELETE FROM Table1 WHERE ID = the ID I'm looking for.

I was looking at how I could avoid using 2 SELECTS and came up with:

SELECT ID1 FROM Table2 WHERE Name IN (SELECT Name FROM Table1 WHERE ID = 1)

I could test the results from that query and if nothing was returned DELETE the record from Table1

The 'problem' I see is that the query will also return nothing if no record is found in Table1 with an ID of 1, so I would end up attempting to DELETE a non-existent record from Table1. Presumably that wouldn't 'matter' but is there a better way of achieving the result ?

[jmcilhinney]

So what you're saying is that you want to delete from Table1 where its Name value does not exist in Table2, right? If so then that's exactly what you do:

DELETE Table1 WHERE Name NOT IN (SELECT Name FROM Table2)

[Doogle]

That's so simple .... I really should have seen it.

Thanks.

[jmcilhinney]

You'd have to test but it might be more efficient to use NOT EXISTS rather than NOT IN, assuming your database supports it or something like it:

DELETE Table1 WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Table2.Name = Table1.Name)

[Doogle]

I know I've marked this as resolved but just to clarify

1.DELETE FROM Table1 WHERE Name NOT IN (SELECT Name FROM Table2)

doesn't that delete all entries in Table1 where Name doesn't exist in Table2?
I'm trying to delete a single record from the table for a given ID

So

DELETE FROM Table1 WHERE NOT IN (SELECT Name FROM Table2) AND ID = 1

would do the trick?

[jmcilhinney]

I know I've marked this as resolved but just to clarify

1.DELETE FROM Table1 WHERE Name NOT IN (SELECT Name FROM Table2)

doesn't that delete all entries in Table1 where Name doesn't exist in Table2?
I'm trying to delete a single record from the table for a given ID

So

DELETE FROM Table1 WHERE NOT IN (SELECT Name FROM Table2) AND ID = 1

would do the trick?

Yes. Mind you, it would be far more efficient for the database to check the ID for a match first and I'm not sure that it will optimise it that way itself so you should put it first in your WHERE clause.