Need Help with Delete Query

[modest]

Table [I]Dogs:[/I]
[B]Name   FirstStop  SecondStop[/B]
Spot    London     Paris
Spot    Paris      Beijing
Spot    Rome       Beijing
Alf     Miami      Key West

Table [I]PlacesToGo:[/I]
[B]Name   Location[/B]
Spot    London
Spot    Paris
Alf     Miami

I need a query to delete all records from "Dogs" where FirstStop AND SecondStop are not a Location.

So in the tables above, the row with Spot - Rome,Beijing would be deleted because Rome is not in PlacesToGo for spot and Beijing is not either.
Spot- Paris,Beijing is okay because Paris is one of the places spot can go.
Spot- Beijing,Paris would also be okay (just showing order doesn't matter)


I have a query that comes close to doing this, but doesn't quite get there

DELETE 
FROM Dogs 
WHERE EXISTS 
( 
    SELECT *
    FROM [PlacesToGo]
    WHERE 
    (
        (Dogs.[FirstStop]=PlacesToGo.[Location] Or 
         Dogs.[SecondStop]=PlacesToGo.[Location])=False
    )
);

I think I'm going to need an iif(), is it possible to have another select query insie the iif?

I could use anyone's help on this. If I do anything logical... it seems to delete all my records.

Thanks in advance,
Modest

 

[FoFa]

This is "off the top of me head" so might need a little tweaking, but try this:
DELETE
FROM DOGS
LEFT JOIN PlacesToGo as PTG1 on DOGS.FirstStop = PTG1.Location
LEFT JOIN PlacesToGo as PTG2 on DOGS.SecondStop = PTG2.Location
WHERE PTG1.Location IS NULL
AND PTG2.Location IS NULL

 

[modest]

Ahhh duplicate tables... I wasn't thinking ... it's been a long week And don't worry about the tweaking. My tables actually have many fields with difficult to understand names. I just made this up as an example so it would be easier for whomever comes across it.

SELECT DISTINCT 
    DOGS.* 
FROM 
    (DOGS
    LEFT JOIN PlacesToGo as PTG1 on DOGS.FirstStop = PTG1.Location) 
    LEFT JOIN PlacesToGo as PTG2 on DOGS.SecondStop = PTG2.Location
WHERE 
(
    (PTG1.Location AND PTG2.Location) IS NULL
);

This works as a select query. It selects 64 of 700 records.

DELETE * FROM DOGS
WHERE EXISTS
(
    SELECT 
        DISTINCT DOGS.* 
    FROM (DOGS
        LEFT JOIN PlacesToGo as PTG1 on DOGS.FirstStop = PTG1.Location) 
        LEFT JOIN PlacesToGo as PTG2 on DOGS.SecondStop = PTG2.Location
    WHERE 
    (
        (PTG1.Location AND PTG2.Location) IS NULL
    )
);

But this does not work as a Delete query. It deletes all my records.

 

[FoFa]

Instead of exists, I would use YourPrimaryKey IN and in your select return the primary key's of those that you wish to delete.

 

[Samoan]

How about

DELETE *
FROM Dogs
WHERE FirstStop not in (SELECT location FROM PlacesToGo)
AND SecondStop not in (SELECT location FROM PlacesToGo)

 

[modest]

Negative on the primary key idea boss....no such number no such zone.

 

[modest]

DELETE *
FROM Dogs
WHERE FirstStop not in (SELECT location FROM PlacesToGo)
AND SecondStop not in (SELECT location FROM PlacesToGo)

No luck either.


Added - I did however add a WHERE to both of the SELECT statements and I think this may be the answer:

DELETE *
FROM Dogs 
WHERE FirstStop not in (SELECT location FROM PlacesToGo WHERE dogs.name = placestogo.name)
AND SecondStop not in (SELECT location FROM PlacesToGo WHERE dogs.name = placestogo.name)

I only think it may me because I received a really small number of returns and am still testing to see if it's the results I wanted. But thanks a lot!... I'm on the right track

 

[modest]

As I mentioned, my tables are much more complex then the ones listed above (and they have nothing to do with dogs or stops ). Therefore this should be used as a template. Furthermore, I don't think I mentioned it, but the basis of my problem was to delete the records where there stops weren't equal to a location.. and the simple part I left out: if there were any stops to be made. So I had to get rid of nulls.

Fofa, I appreciate your help. I still don't understand why either of our ways didn't work... and Samoan, what can I say? I owe you my gratitude.

The final code looks like this:

DELETE *
FROM 
    Dogs 
WHERE 
(
    Dogs.Name Is Not Null 
    AND (Dogs.FirstStop Or Dogs.SecondStop) Is Not Null 
    AND FirstStop Not In 
               (SELECT location 
                FROM   PlacesToGo 
                WHERE  Dogs.Name = PlacesToGo.Name) 
    AND SecondStop Not In 
               (SELECT location 
                FROM   PlacesToGo 
                WHERE  Dogs.Name = PlacesToGo.Name)
);

 

[FoFa]

Well access does not handle subselects that well (it handles them, just rather slowly), so I always try to work around that and use it as a last resort. Since Samoan posted that, that was the next resort. Exists is a hit or miss thing, does not suprise me. Any way, glad it is resolved.

 

[modest]

Well access does not handle subselects that well (it handles them, just rather slowly), so I always try to work around that and use it as a last resort. Since Samoan posted that, that was the next resort. Exists is a hit or miss thing, does not suprise me. Any way, glad it is resolved.

Yes, sub selects really make it crawl. If it gets that slow.. I do an append to put it in a new table and then do another append and then do a delete. It's more queries, but it runs faster then Exists (Select)