duplicate records on my bill of lading please help!

[jcisco2]

I have a bill of lading that is getting duplicate records (which is very very bad.) and i need to remove them with a select statement. and i'm having some problems doing so. i have looked at other faq's on this subject and they are not helping me out. (i've also looked at the suggested web sites. this issue is very urgent. any help would be very very helpful.)

when i run this:

SELECT ItemNumber, Count(ItemNumber)
FROM tbl_TempBillOFLading
GROUP BY tbl_TempBillOFLading.ItemNumber, tbl_TempBillOFLading.ItemNumber
HAVING Count(tbl_TempBillOFLading.ItemNumber) > 1

i get this as a result.
ItemNumber (no column name)
11803 2

thus it found two records in my bill with the same data.
I need a way to remove this with a delete. can someone please help?

thank you.

 

[NathanGriffiths]

A bit more info about your table structure would help and some example data.

Also is there a typo in the code you quoted, as you seem to be grouping by ItemNumber twice...

Does your table have an identity column, or a unique key?



Nathan


Want to get a good response to your question? Read this FAQ! -> faq183-874

 

[Shal2]

Will distinct keyword help?
Shal

 

[donutman]

We need the info Nathan asked for, but if you don't have a primary key how about making the ItemNumber a primary key. That will help you find out why multiple ItemNumber values are getting into your database (the application will bomb), i.e. fix the problem at its source. If you're a purist create an identity column for the pk and put a unique constraint on ItemNumber.
-Karl

 

[jcisco2]

yeah.
I have a column called billcount which is a Identity so i could use that as a pkey. but it is currently not one. As for the select distinct I would use that but my problem is some of my itemUPC's are wrong. what i was going to do is correct the UPC's after i cleaned up the data. (it's the upc's that are causing all my woes. )
I was going to fix it at the application level, but it would be alot simpler to fix it at a sql level.
----

sample data would be.
LoadNumber (varchar) ItemNumber ItemUPC billCOUNT
27562 11073 7089715511073 1
27562 11073 7899988851576 2

thank you for the help.

 

[jcisco2]

got it.
thanks for the help

answer was using my billCount in this statement.

DELETE tbl_TempBillOFLading
WHERE billcount NOT IN
(
SELECT MIN(billcount)
FROM tbl_TempBillOFLading copy
WHERE
tbl_TempBillOFLading.ItemNumber = copy.ItemNumber
AND tbl_TempBillOFLading.ItemNumber = copy.ItemNumber
)

cheers.