?
Solved

Delete query WHERE exist in second tbl

Posted on 2003-03-04
5
Medium Priority
?
1,449 Views
Last Modified: 2007-12-19

I need to make a query that will delete records as follows:

If [ID] exists in [Tbl2] then DELETE row from [Tbl1] WHERE [ID] is the same.

I have tried many variants on this but get errors saying:

"Specify the table containing the records you want to delete."
or
"Couldn't delete from specified tables"

I'm at a loss on this one. Please help!

dm
0
Comment
Question by:dathmagar
[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 Comments
 
LVL 7

Accepted Solution

by:
TroyK earned 200 total points
ID: 8067531
dathmagar;

CREATE TABLE Tbl1(
  [ID] int NOT NULL PRIMARY KEY,
  SomeData varchar(10) NOT NULL
)
GO

CREATE TABLE Tbl2(
  MyID int NOT NULL PRIMARY KEY,
  [ID] int NOT NULL, --We'll skip the FK relationship for this sample
  OtherData varchar(10) NOT NULL
)
GO

INSERT INTO Tbl1
SELECT 1, 'test1' UNION ALL
SELECT 2, 'test2'

INSERT INTO Tbl2
SELECT 100, 1, 'data1' UNION ALL
SELECT 300, 3, 'data3'
GO

SELECT [ID], SomeData FROM Tbl1
GO

--Here's the statement you're after...
DELETE Tbl1
WHERE EXISTS(SELECT *
            FROM Tbl2
            WHERE [ID] = Tbl1.[ID])
GO

SELECT [ID], SomeData FROM Tbl1
GO

DROP TABLE Tbl1, Tbl2
GO

HTH,
TroyK, MCSD
0
 
LVL 2

Expert Comment

by:kraig
ID: 8067656
How about:

DELETE * FROM tbl1 WHERE ID IN (SELECT ID FROM tbl2)
0
 

Expert Comment

by:nickah
ID: 8068622
Hello all,
Kraig's comment is good except he needs to drop the * from the command.  
Specifically,
DELETE * FROM tbl1 WHERE ID IN (SELECT ID FROM tbl2)


An alternate method follows.  ANSI SQL (which Microsoft supports in this case) allows two FROM clauses for these situations and more complex situations.

DELETE FROM tableWithRows2Delete
FROM tableWithRows2Delete delT inner join otherTable otherT
on delt.id = otherT.id

BTW, if this answers your question please credit someone with the points, Thank you.
0
 

Expert Comment

by:nickah
ID: 8068629
Whoops, I mean Kraig's command should be
DELETE FROM tbl1 WHERE ID IN (SELECT ID FROM tbl2)
I guess I should take my own advice, ha ha.

Nick
0
 
LVL 1

Author Comment

by:dathmagar
ID: 8068700
A little bit beyond me but i have been shown how to put it into practice and it works fine.

Thanks for the learning
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

762 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