?
Solved

SQL Syntax - Delete Records based on another tables data

Posted on 2010-09-08
3
Medium Priority
?
323 Views
Last Modified: 2012-05-10
Hi

I have the following 2 tables:

tblProducts
Columns: ProductType, ReleaseDate, CustomerType

tblDormantProducts
Columns: ProductType, DateFrom, DateTo, CustomerType

How could I create an SQL DELETE statement to delete records from tblProducts based on records that match within tblDormantProducts?...

with tblProducts.ReleaseDate, this needs to match between tblDormantProducts.DateFrom and tblDormantProducts.DateTo.

Many thanks,

Rit
0
Comment
Question by:rito1
[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
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 33628560
DELETE FROM tblProducts
FROM tblProducts p INNER JOIN
    tblDormantProducts d ON p.ProductType = d.ProductType
WHERE p.ReleaseDate BETWEEN d.DateFrom AND d.DateTo
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33631808
Ceck this replace with your table and field names.
Table A (a, x)
Table B (a, b, x1, x2)

DELETE  * FROM A
WHERE A.a
IN (SELECT  A.a
FROM A INNER JOIN B ON A.a = B.a
WHERE A.x Between B.x1 AND B.x2)
0
 
LVL 1

Author Closing Comment

by:rito1
ID: 33637235
Thanks both but I went with matthewspatrick purely because I set to work on his syntax and it all made sense to me as I was implementing.

many thanks,

Rit
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

800 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