Solved

SQL: Delete query using a join

Posted on 2008-10-13
19
1,117 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now