'I need to compare data from the same column within the same table
I am trying to write a SQL query to find duplicate data entries that aren't exact duplicates. For example, I have a list of items with codes. If the same item is entered twice, the duplicate entry has a -[#] after it. Some entries have a hyphen even though it is not a duplicate. Here is an example table:
ID Code ItemID ItemName
1 11111 11 Ball -Keep
2 22222 22 Kite -Keep
3 11111-1 11 Balls -Delete
4 11111-2 11 Ball(s) -Delete
5 33333-1 33 Shoe -Keep, but remove the '-1'
None of the items should have a hyphen in the code, so I need to make sure I delete the duplicate rows. I can't just delete the codes that have a hyphen because SOME of the codes with a hyphen do not have a matching code without the hyphen. The ItemName does not need to match, but the ItemID does.
I would like to find a way to compare the data in the Code column against itself to find all entries that have a hyphen AND also have a matching non-hyphen entry. I can then delete those entries. Is there a way to do this?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
