Delete from a table a string of specific values

mtrussell
mtrussell used Ask the Experts™
on
I know I am close on this but how do I delete values from a table (where the key identifier is in a linked table)?

This is what I have but I get I am missing something...

DELETE *
FROM Tbl_ClientFlightControl
WHERE Location IN (SELECT Location FROM tbl_regions WHERE Region Like "Asia Only" Or "EMEA Only" Or "EMEA and Asia" Or "N/A");
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
You haven't properly formed the conditional statements.

DELETE *
FROM Tbl_ClientFlightControl
WHERE Location IN (SELECT Location FROM tbl_regions WHERE Region Like "Asia Only" Or Region Like "EMEA Only" Or Region Like "EMEA and Asia" Or Region Like "N/A");

Open in new window


Note that a condition such as

Region Like "Asia Only"

is effectively the same as:

Region = "Asia Only"

If your intent is "contains 'Asia Only'", you would write it like this:

Region Like "*Asia Only*"

(note the use of wild cards)
Top Expert 2010
Commented:
Or:

DELETE *
FROM Tbl_ClientFlightControl
WHERE Location IN (SELECT Location FROM tbl_regions WHERE Region In ("Asia Only", "EMEA Only", "EMEA and Asia", "N/A"));

Open in new window

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial