CODEWITHSUNDEEP
mysqlsqlsql-update
James B
James B

Reputation: 213

Update field based on duplicate values

I'm trying to update the value of a column based on duplicates in my database, but I'm struggling to find the correct query.

Here's my db structure:

id | product_id | order_id | parent_id
--------------------------------------
1        1           1         SMITH1
2        1           2         SMITH1
3        2           3         BLOGGS1
4        2           4         BLOGGS1

I want to update the order_id to be the same, where we have duplicates of both product_id AND parent_id.

Currently I've found the duplicates like so:

SELECT 
    *, 
    COUNT(parent_id),
    COUNT(product_id)
FROM
    mytable
GROUP BY parent_id, product_id
HAVING COUNT(parent_id) > 1
AND COUNT(product_id) > 1

But I'm now struggling with the update/join to set the order_id values the same (can be minimum order_id value preferably).

Any help appreciated!

Upvotes: 1

Views: 54

Answers (1)

forpas
forpas

Reputation: 164184

With a join of the table to the minimum value of order_id for each combination of product_id and parent_id:

update mytable t inner join (
  select product_id, parent_id, min(order_id) order_id
  from mytable
  group by product_id, parent_id
  having min(order_id) <> max(order_id)
) tt on tt.product_id = t.product_id and  tt.parent_id = t.parent_id
set t.order_id = tt.order_id;

This code will prevent any unnecessary updates in case there is only one order_id for that product_id and parent_id.
See the demo.
Results:

| id  | product_id | order_id | parent_id |
| --- | ---------- | -------- | --------- |
| 1   | 1          | 1        | SMITH1    |
| 2   | 1          | 1        | SMITH1    |
| 3   | 2          | 3        | BLOGGS1   |
| 4   | 2          | 3        | BLOGGS1   |

Upvotes: 2

Related Questions