'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