SQL Syntax - Delete Records based on another tables data

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
LVL 1
rito1Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
hnasrCommented:
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
 
rito1Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.