Ask Your Question
0

SQL for deleting records in Tbl1 that Match Tbl2.

asked 2015-07-05 18:06:56 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

What is the SQL statement for a Delete Query where the objective is to delete the records in a table that are equal to the records in another table that is joined to it? For example, you have a table that contains phone numbers of opportunity seekers joined to a table that contains phones numbers of people who have opted out of being contacted. So the objective is to delete the records in the table of opportunity seekers if their phone number is also in the table of opt outs.

Here is the SQL statement from the same query in Access:

DELETE [Text-Connects].*
FROM [Text-Connects] 
INNER JOIN [Text-Removals] ON [Text-Connects].Mobile = [Text-Removals].Mobile;
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-07-06 03:36:01 +0200

doug gravatar image

As you may already have discovered, the ordinary SQL syntax for this does not seem to work in HSQLDB. However, here is a way to re-write the query that tests ok on at least one trial run on my side. Please note that since this is a DELETE query, obviously it is data-destructive and the IN constraint that I use below is fickle and so I would back up your data and only run on something you would not be sad to lose before you thoroughly test on your system.

DELETE
FROM "Text-Connects"
WHERE "Mobile" IN(SELECT "Mobile" FROM "Text-Removals")

This run from Tools--> SQL

If this answers your question please accept the answer by clicking the check mark (image description) to the left.

edit flag offensive delete link more

Comments

Thanks Doug! Your SQL statement does work but it must be run by clicking Tools and then SQL which I failed to do when I first tried it.

donnob gravatar imagedonnob ( 2015-07-06 17:11:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-05 18:06:56 +0200

Seen: 102 times

Last updated: Jul 06 '15