Delete unmatched records

[mark007]

I have two tables invoice and so_salesorderdetail1, I want to delete those records from invoice which are not in so_salesorderdetail1.

these tables don't have any unique key so I am trying to make a composite key. I wrote the following two different queries but it doesn't seem working.

1.
DELETE FROM INVOICE
WHERE NOT EXISTS
(SELECT SO_SalesOrderDetail1.SalesOrderNo, SO_SalesOrderDetail1.ItemCode
FROM SO_SalesOrderDetail1 INNER JOIN INVOICE ON (SO_SalesOrderDetail1.ItemCode = INVOICE.Item_Code) AND (SO_SalesOrderDetail1.SalesOrderNo = INVOICE.Sales_Order));

2.
DELETE INVOICE.*
FROM INVOICE
WHERE (((INVOICE.Sales_Order) Not In (SELECT SalesOrderNo from SO_SalesOrderDetail1)) AND ((INVOICE.Item_Code) Not In (Select ItemCode from SO_SalesOrderDetail1)));


Is there any other way I can delete unmatched records.


Mark

[Delerna]

Lets see if I can say this in a way that makes sense
In your second query you are saying
if the [INVOICE].[Sales_Order] number doesn't exist in the [SO_SalesOrderDetail1] table then I might want to delete this record but I had better make another check first.

That other check is
if the [INVOICE].[Item_Code] on that missing [INVOICE].[Sales_Order]
isn't used on ANY other order in the [SO_SalesOrderDetail1] table
then delete the record.

It's hard to tell because you haven't provided much info but I don't think that is what you meant to say.

My gut feeling is that the Item_Code on a missing order would surely be used on one or more other orders and therefore your query will never delete anything.


Is it not enough just to check [Sales_Order] for non existance?
If you want to experiment make a backup copy of the table and experiment on that.
Also, maybe instead of just deleting them you should insert them into a deleted records table first? Especially if this is live data.

That way you can always put them back in the future if you discover one or more should not have been deleted

[mark007]

Thanks for your input Delerna...

I am new in this area...so any help is appreciated

Just checking on Sales_Order for non existence does not give me the expected results..as this is not a unique key…

Let me try to explain...

If there are 4 items i1,i2,i3,i4 under sales order ABC…

I will have 4 rows and sales order will be ABC for all 4 items..only way it can be differentiated is by combining item code and sales order


SalesOrder ItemCode
ABC i1
ABC i2
ABC i3
ABC i4

I want to compare INVOICE and SO_SalesOrderDetail1 table and want to delete those from INVOICE which are not in SO_SalesOrderDetail1

If i1 and i2 are shipped out from Sales Order ABC...It will not be there in SO_SalesOrderDetail1 and I want to delete only these two rows from INVOICE.

This select query gives me the result I want, but I could not make it delete query somehow.

SELECT INVOICE.*
FROM INVOICE LEFT JOIN SO_SalesOrderDetail1 ON (INVOICE.Item_Code = SO_SalesOrderDetail1.ItemCode) AND (INVOICE.Sales_Order = SO_SalesOrderDetail1.SalesOrderNo)
WHERE (((SO_SalesOrderDetail1.SalesOrderNo) Is Null) AND ((SO_SalesOrderDetail1.ItemCode) Is Null));

[Delerna]

From your sample data I see that the two fields are of some char type

so what about this

DELETE INVOICE.*
FROM INVOICE
WHERE INVOICE.Sales_Order & INVOICE.Item_Code 
Not In (   SELECT SalesOrderNo & Item_Code 
           from SO_SalesOrderDetail1);
 

[mark007]

It worked :)

Thanks Delerna....