Delete Query for records that do not exist in second table (Purging old records)

Hello, I have tried using the query below to delete records that exist in the SHIPPINGFORECAST table, but they do not exist in the neword01 table. That select query gives me the correct result set, but when I run it with the delete it removes all records from the SHIPPINGFORECAST table. Any help would be appreciated.

DELETE FROM SHIPPINGFORECAST
WHERE EXISTS
(SELECT dbo.SHIPPINGFORECAST.ORDERNUMBER, dbo.SHIPPINGFORECAST.JOBNAME, dbo.SHIPPINGFORECAST.SHIPDATE
FROM dbo.SHIPPINGFORECAST LEFT OUTER JOIN
dbo.neword01 ON dbo.SHIPPINGFORECAST.ORDERNUMBER = dbo.neword01.ordn07
WHERE (dbo.neword01.ordn07 IS NULL))

Hai,

As you have not mentioned which table is master and which is transaction we assumed @.ShippingForeCast as Master and @.NewOrd01 as transaction table and concluded the below query:

Please Clarify If'm wrong.

DECLARE @.ShippingForecast table(OrderNumber int, JobName varchar(50), ShipDate smalldatetime)

DECLARE @.NewOrd01 table(OrdN07 int)

DECLARE @.CurrentDate smalldatetime

SET @.CurrentDate = getdate()

-- Table - 1

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(1, 'A', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(2, 'B', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(3, 'C', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(4, 'D', @.CurrentDate)

INSERT INTO @.ShippingForeCast(OrderNumber, JobName, ShipDate) VALUES(5, 'E', @.CurrentDate)

-- Table - 2

INSERT INTO @.NewOrd01(OrdN07) VALUES(1)

INSERT INTO @.NewOrd01(OrdN07) VALUES(1)

INSERT INTO @.NewOrd01(OrdN07) VALUES(2)

INSERT INTO @.NewOrd01(OrdN07) VALUES(2)

INSERT INTO @.NewOrd01(OrdN07) VALUES(3)

INSERT INTO @.NewOrd01(OrdN07) VALUES(3)

-- Before Delete

SELECT * FROM @.ShippingForeCast

DELETE FROM @.ShippingForeCast

WHERE OrderNumber IN (

SELECT

S.OrderNumber

FROM @.ShippingForeCast AS S

LEFT JOIN @.NewOrd01 AS N

ON N.OrdN07 = S.OrderNumber

WHERE N.OrdN07 IS NULL )

-- ( OR )

--DELETE FROM @.ShippingForeCast WHERE OrderNumber NOT IN (SELECT OrdN07 FROM @.NewOrd01)

-- After Delete

SELECT * FROM @.ShippingForeCast

Regards,

Prashanthi.

|||

Worked like a charm! Thanks!