Link to home
Start Free TrialLog in
Avatar of rito1
rito1

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
Avatar of rito1
rito1

ASKER

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