Remove duplicate data based on ID

[smellyalater]

Hi Guys

I am wanting to remove certain parts of data from a table.

Here is my data:

+-----------+--------------+--------------+
| Unique ID | Product Cost | Product Type |
+-----------+--------------+--------------+
| 62208     | £89.99       | Main         |
+-----------+--------------+--------------+
| 62208     | £24.99       | Upsell       |
+-----------+--------------+--------------+
| 62208     | £49.99       | Main         |
+-----------+--------------+--------------+
| 62209     | £24.99       | Main         |
+-----------+--------------+--------------+
| 62209     | £49.99       | Main         |
+-----------+--------------+--------------+

I want to go through and look at the unique id, if it has more than 1 main then replace the other will upsell.

Any help is appreciated!

Thanks guys!

 

[arnelgp]

do you want to delete those records when upsell is found in [product type]:

delete * from table where [Product Type] = "Main" And [Unique ID] In (Select [Unique ID] from table Where [Product Type] = "upsell")

it will only delete records if in this [Unique ID] there exists an "upsell" record.
it will not delete the "upsell" record.

 

[smellyalater]

do you want to delete those records when upsell is found in [product type]:

delete * from table where [Product Type] = "Main" And [Unique ID] In (Select [Unique ID] from table Where [Product Type] = "upsell")

it will only delete records if in this [Unique ID] there exists an "upsell" record.
it will not delete the "upsell" record.

hi arnel

I want to remove the 'main' from any unique ids that have any more than one so it would look like this after running the code:

+-----------+--------------+--------------+
| Unique ID | Product Cost | Product Type |
+-----------+--------------+--------------+
| 62208     | £89.99       | Main         |
+-----------+--------------+--------------+
| 62208     | £24.99       | Upsell       |
+-----------+--------------+--------------+
| 62208     | £49.99       |              |
+-----------+--------------+--------------+
| 62209     | £24.99       | Main         |
+-----------+--------------+--------------+
| 62209     | £49.99       |              |
+-----------+--------------+--------------+

 

[arnelgp]

do you have autonumber (say ID) field in the table it would be easy if you include it:

UPDATE Table1 SET Table1.[Product Type] = Null
WHERE [ID] NOT IN (SELECT ID FROM (SELECT DISTINCT T1.[Unique ID], T1.[Product Type], CLng(DLookUp("id","table1","[Unique ID] = '" & [T1].[Unique ID] & "' And [Product Type] = '" & T1.[Product Type] & "'")) AS ID
FROM Table1 AS T1))

 

[smellyalater]

do you have autonumber (say ID) field in the table it would be easy if you include it:

UPDATE Table1 SET Table1.[Product Type] = Null
WHERE [ID] NOT IN (SELECT ID FROM (SELECT DISTINCT T1.[Unique ID], T1.[Product Type], CLng(DLookUp("id","table1","[Unique ID] = '" & [T1].[Unique ID] & "' And [Product Type] = '" & T1.[Product Type] & "'")) AS ID
FROM Table1 AS T1))

I don't arnel but it will be easy to add so I will try this code.

Thank you very much!

 

[arnelgp]

goodluck sir!

 

[smellyalater]

Thank you arnel it works perfectly!

Have a great day!