Solved

SQL: Delete query using a join

Posted on 2008-10-13
19
1,122 Views
Last Modified: 2008-10-20
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]
0
Comment
Question by:ShockUK
  • 5
  • 4
  • 4
  • +4
19 Comments
 

Author Comment

by:ShockUK
ID: 22701706
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
 
LVL 8

Expert Comment

by:Jupiler78
ID: 22701767
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
 

Author Comment

by:ShockUK
ID: 22701786
Came back with a syntax error on the from clause doing it this way
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:Jupiler78
ID: 22701823
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
 

Author Comment

by:ShockUK
ID: 22701868
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22719942
DELETE from t
FROM [adhoc table] t INNER JOIN [Account Numbers Top 100] ON  t.[Acc Num]= [Account Numbers Top 100].[Acc Num];
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22719950
How about this?

DELETE  FROM [adhoc table]
WHERE [adhoc table].[Acc Num] IN (Select [Acc Num] From [Account Numbers Top 100])
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22720515
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22721029
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22721210
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22721219
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22721428
zbertoc that is the correct approach, but I think the table is actually named [Account Numbers Top 100]  :)
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 22721665
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
 

Author Comment

by:ShockUK
ID: 22721867
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22757454
@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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22757557
True, TheLearnedOne gave exactly the same solution before me. If possible I would advise to transfer the points to her/him.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22758122
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22758466
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question