[RESOLVED] Sql 'in'

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 ?

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:

SQL Code:

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

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

Thanks.

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:

SQL Code:

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

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

Code:

---

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

Code:

---

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

---

would do the trick?

Quote:

---

Originally Posted by Doogle

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

Code:

---

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

Code:

---

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.