CODEWITHSUNDEEP
phpmysql
user1227914
user1227914

Reputation: 3524

how to update duplicate mySQL records based on a query

I have 2 tables called db_carts and db_carts_items.

The problem is that they may have different sc_id but the same seller_id.

I am trying to get ones that have duplicates (which I achieved with the query below) and then I need to update the duplicate ones so that for each different seller_id the sc_id is the same (the highest value of the matches, if possible).

How would I achieve this, preferably in mySQL or PHP?

Here is the query:

SELECT * FROM db_carts 
LEFT JOIN db_carts_items ON db_carts.sc_id=db_carts_items.sc_id
WHERE buyer_id="123456"
AND seller_id IN 
    ( SELECT seller_id
      FROM db_carts 
      WHERE buyer_id="123456"
      GROUP BY seller_id having (count(*) > 1)
    )

And that gives me an output like:

sc_id    |    seller_id    |    buyer_id    |
=============================================
15             50                123456
18             50                123456
23             70                123456
45             70                123456

And I need it to update db_carts_items so that it looks like:

sc_id    |    seller_id    |    buyer_id    |
=============================================
18             50                123456
18             50                123456
45             70                123456
45             70                123456

Is that in any way possible from mySQL? If not, in PHP?

I basically need to update the sc_id value in the db_carts_items table based on that output, for each of the duplicates.

Upvotes: 1

Views: 50

Answers (1)

Kevin
Kevin

Reputation: 120

Something like this should do the trick:

with duplicates as (SELECT seller_id, max(sc_id) as maximum 
FROM db_carts 
WHERE buyer_id='123456'
GROUP BY seller_id
HAVING count(*)>1)
UPDATE db_carts, db_carts_items 
set db_carts.sc_id=duplicates.maximum, 
db_carts_items.sc_id=duplicates.maximum
FROM db_carts, db_carts_items, duplicates
WHERE db_carts.buyer_id='123456'
AND db_carts.seller_id=db_carts_items.seller_id 
AND db_carts.seller_id=duplicates.seller_id;

Edit: It seems like you can't use with on an update statement. In this case I would just do it with PHP. First fetch the seller_id and their maximum

"SELECT seller_id, max(sc_id) as maximum 
FROM db_carts 
WHERE buyer_id='123456'
GROUP BY seller_id
HAVING count(*)>1"

Then for each returned row make an update like this

"UPDATE db_carts, db_carts_items 
set db_carts.sc_id=".$row['maximum'].", 
db_carts_items.sc_id=".$row['maximum'].
"FROM db_carts, db_carts_items
WHERE db_carts.buyer_id='123456'
AND db_carts.seller_id=db_carts_items.seller_id 
AND db_carts.seller_id=".$row['seller_id']

Upvotes: 2

Related Questions