Nenad Maric Nenad Maric - 5 months ago 15
MySQL Question

Update table equalize where same id but different country

I have a problem creating an SQL statement that would run in a cronjob every few minutes.

I want to equalize one column where

ItemCode
s (IDs) are the same but each country is different

Table:

+----------+--------+---------+

| ItemCode | OnHand | country |

+----------+--------+---------+

| 08040 | 450 | de |

+----------+--------+---------+

| 08040 | 000 | hu |

+----------+--------+---------+

| 08040 | 145 | si |

+----------+--------+---------+


I want to take
OnHand where country = de
and put it in
OnHand where country = hu
. (for multiple
ItemCode
- have 7000 of them)

UPDATE sap_items
ON sap_items.ItemCode = sap_items.ItemCode AND
sap_items.country='de'
SET sap_items.OnHand = sap_items.OnHand
WHERE sap_items.country='hu'";


I understand that this is wrong. But i'm not sure how to properly put it.

Answer

Your current syntax is off. You could do an UPDATE combined with a self join:

UPDATE sap_items AS t1
INNER JOIN sap_items AS t2
    ON t1.ItemCode = t2.ItemCode
SET t1.OnHand = t2.OnHand
WHERE t1.country = 'hu' AND t2.country = 'de'