Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL: Delete query using a join

Posted on 2008-10-13
19
Medium Priority
?
1,131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 60

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 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 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 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 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 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 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 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

715 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