?
Solved

SQL: Delete query using a join

Posted on 2008-10-13
19
Medium Priority
?
1,140 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
18 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 61

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 53

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 27

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 27

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 53

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 27

Accepted Solution

by:
Zberteoc earned 2000 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 53

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 27

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 53

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 27

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

568 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