Delete records in on table only

[jslmvl]

Hi,

By the help from tek-tipx.com I can run the sql:
"Select * From Table1 Left Join (Select Table2.[User] From Table2 Group By Table2.[User]) As t On Table1.[User]=t.[User]"

Now, I want to delete the selected records in Table1 something like
"Delete * From Table1 Left Join (Select Table2.[User] From Table2 Group By Table2.[User]) As t On Table1.[User]=t.[User] Where t.[User] Is Null"
without success. Can you tell me how to do that?

Thanks a lot for any help in advance.

 

[PHV]

I'd try this:
DELETE FROM Table1 WHERE [User] NOT IN (SELECT DISTINCT [User] FROM Table2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jslmvl]

PHV,

Thank you for your suggestion which looks nice.

However, in my case (a very big .mdb), it took very very very long time to do
"Select FROM Table1 WHERE [User] NOT IN (SELECT DISTINCT [User] FROM Table2)"
while to do following is very fast
"Delete * From Table1 Left Join (Select Table2.[User] From Table2 Group By Table2.[User]) As t On Table1.[User]=t.[User] Where t.[User] Is Null

 

[jslmvl]

Sorry, I meant
while to do following is very fast
"Select * From Table1 Left Join (Select Table2.[User] From Table2 Group By Table2.[User]) As t On Table1.[User]=t.[User] Where t.[User] Is Null"
and that is why I want to do something like

"Delete * From Table1 Left Join (Select Table2.[User] From Table2 Group By Table2.[User]) As t On Table1.[User]=t.[User] Where t.[User] Is Null

 

[PHV]

What about this ?
Delete Table1.* From Table1 Left Join Table2 On Table1.User=Table2.User Where Table2.User Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jslmvl]

I tested that can work but again runs endless....

 

[kjv1611]

When the delete command is being run, do you have the Table1 records locked by another query, user form, or other?

--

"If to err is human, then I must be some kind of human!" -Me