Delete unmatched records

[mark007]

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

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_SalesOrderDe tail1.SalesOrde rNo, SO_SalesOrderDe tail1.ItemCode
FROM SO_SalesOrderDe tail1 INNER JOIN INVOICE ON (SO_SalesOrderD etail1.ItemCode = INVOICE.Item_Co de) AND (SO_SalesOrderD etail1.SalesOrd erNo = INVOICE.Sales_O rder));

2.
DELETE INVOICE.*
FROM INVOICE
WHERE (((INVOICE.Sale s_Order) Not In (SELECT SalesOrderNo from SO_SalesOrderDe tail1)) AND ((INVOICE.Item_ Code) Not In (Select ItemCode from SO_SalesOrderDe tail1)));


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_SalesOrderDe tail1] 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_SalesOrderDe tail1] 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_SalesOrderDe tail1 table and want to delete those from INVOICE which are not in SO_SalesOrderDe tail1

If i1 and i2 are shipped out from Sales Order ABC...It will not be there in SO_SalesOrderDe tail1 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_SalesOrderDe tail1 ON (INVOICE.Item_C ode = SO_SalesOrderDe tail1.ItemCode) AND (INVOICE.Sales_ Order = SO_SalesOrderDe tail1.SalesOrde rNo)
WHERE (((SO_SalesOrde rDetail1.SalesO rderNo) Is Null) AND ((SO_SalesOrder Detail1.ItemCod e) Is Null));

[Delerna]

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

so what about this
[code=sql]
DELETE INVOICE.*
FROM INVOICE
WHERE INVOICE.Sales_O rder & INVOICE.Item_Co de
Not In ( SELECT SalesOrderNo & Item_Code
from SO_SalesOrderDe tail1);
[/code]

[mark007]

It worked :)

Thanks Delerna....