SQL: Delete query using a join

Hello,
I have a table called AdHoc which contains my main data and I have a 2nd table with just account numbers. I want to delete the accounts from Adhoc Table which are in the 2nd Table. I have created the following but it doesn't work. Can anyone help?

DELETE *
FROM [adhoc table]
WHERE [adhoc table].[Acc Num]=[Account Numbers Top 100].[Acc Num]
ShockUKAsked:
Who is Participating?
 
ZberteocCommented:
Then this shoul do it:
DELETE FROM [adhoc table]
WHERE 
	[Acc Num] IN ( SELECT [Acc Num] FROM [Account Numbers Top 100] )

Open in new window

0
 
ShockUKAuthor Commented:
Also now tried the following but it asks which table to delete.

DELETE *
FROM [adhoc table] INNER JOIN [Account Numbers Top 100] ON  [adhoc table].[Acc Num]= [Account Numbers Top 100].[Acc Num];
0
 
Jupiler78Commented:
Hello ShockUK,

try:

DELETE * FROM [adhoc table]
FROM [adhoc table] INNER JOIN [Account Numbers Top 100] ON  [adhoc table].[Acc Num]= [Account Numbers Top 100].[Acc Num];

I know it had to be done that way in Sybase.


Regards,

Jupiler78
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ShockUKAuthor Commented:
Came back with a syntax error on the from clause doing it this way
0
 
Jupiler78Commented:
Ok, I thought so already, but it was worth the try.

Try to remove the * as you delete the whole record, not a field.
So: Delete from ...
0
 
ShockUKAuthor Commented:
Nope, this is annoying. I'm using Access to run the script if that is any different. Also tried

DELETE  FROM [adhoc table]
USING  [adhoc table], [Account Numbers Top 100]
WHERE[adhoc table].[Acc Num]= [Account Numbers Top 100].[Acc Num];

with no joy
0
 
momi_sabagCommented:
DELETE from t
FROM [adhoc table] t INNER JOIN [Account Numbers Top 100] ON  t.[Acc Num]= [Account Numbers Top 100].[Acc Num];
0
 
Bob LearnedCommented:
How about this?

DELETE  FROM [adhoc table]
WHERE [adhoc table].[Acc Num] IN (Select [Acc Num] From [Account Numbers Top 100])
0
 
Kevin CrossChief Technology OfficerCommented:
For what it is worth, I think this is how it is done in SQL server and may work with MS Access, but the IN statement should work fine that Learned just posted.

DELETE t
FROM [adhoc table] t
INNER JOIN [Account Numbers Top 100] a
ON t.[Acc Num]= a.[Acc Num];
0
 
Mark WillsTopic AdvisorCommented:
Access if a bit of a prig when it comes to joined tables. Thinks you are wanting both, or one is a parameter...

Can use DELETE DISTINCTROW table inner join other_table on ....

and the rows that match the other_table join will be deleted from  table.

BUT, best to use TheLearnedOne's example and resolve the join by avoiding it altogether by checking if exists or IN subquery etc...
0
 
ZberteocCommented:
Try this:
DELETE FROM [adhoc table]
WHERE 
	[Acc Num] IN ( SELECT TOP 100 [Acc Num] FROM [Account Numbers Top 100].[Acc Num] ORDER BY [Acc Num] )

Open in new window

0
 
ZberteocCommented:
Correction:
DELETE FROM [adhoc table]
WHERE 
	[Acc Num] IN ( SELECT TOP 100 [Acc Num] FROM [Account Numbers] ORDER BY [Acc Num] )

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
zbertoc that is the correct approach, but I think the table is actually named [Account Numbers Top 100]  :)
0
 
ShockUKAuthor Commented:
Wow lots to go off thanks for the support.
I'm going to be back in work tomorrow and I will test them and let ou all know

Thank you very mcuh
0
 
Mark WillsTopic AdvisorCommented:
@shockUK - what was wrong with thelearnedone's submission ? I do not see it to be different to the corrections given to zberteoc to attain the "accepted" solution - just curious that is all.
0
 
ZberteocCommented:
True, TheLearnedOne gave exactly the same solution before me. If possible I would advise to transfer the points to her/him.
0
 
Mark WillsTopic AdvisorCommented:
Zberteoc, very noble, but I was not for one instant suggesting the points need to be reassigned - I was curious to see if the submission had even been looked at...
0
 
ZberteocCommented:
Myself too I omitted to check all the answers otherwise I would not have posted the same solution again. Omissions happen so there should be a way to correct the points awarding.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.