By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

Delete unmatched records

P: 5
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
Feb 28 '10 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
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
Feb 28 '10 #2

P: 5
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));
Mar 1 '10 #3

Delerna
Expert 100+
P: 1,134
From your sample data I see that the two fields are of some char type

so what about this
Expand|Select|Wrap|Line Numbers
  1. DELETE INVOICE.*
  2. FROM INVOICE
  3. WHERE INVOICE.Sales_Order & INVOICE.Item_Code 
  4. Not In (   SELECT SalesOrderNo & Item_Code 
  5.            from SO_SalesOrderDetail1);
  6.  
Mar 1 '10 #4

P: 5
It worked :)

Thanks Delerna....
Mar 2 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.