Update table A with info of B with value as a column name

Question

Tabel A:

Id |sku   |Country       |pricecountry
1   b1     Netherlands     *null*

Table B:

sku    |Germany  |France   |Netherlands
b1     3,88       7,55      6,14

The goal is to update column pricecountry in Table A with sku1. In this case it should be 6,14

I think something like this but don't know..

UPDATE tableA as a SET
 a.pricecountry = ( select column(a.country)
                        FROM tableB as b
                        WHERE a.sku = b.sku  and
                        column(a.country)

 );

Table b is not well designed. If you have the option you should try to normalise (eg sku,country,value) — P.Salmon, Dec 19 '17 at 12:14
@P.Salmon Well, then B's structure would match A's structure. So, perhaps that's what the Op is trying to achieve? So normalise B in to A? — MatBailie, Dec 19 '17 at 12:23

Valerica · Accepted Answer · 2017-12-19T12:18:52.040

You need to UNPIVOT tableB and JOIN it to tableA:

update tableA as A
inner join (
    -- unpivot start
    select sku, 'Germany' as country, Germany as value
    from tableB
    union all
    select sku, 'France' as country, France as value
    from tableB
    union all
    select sku, 'Netherlands' as country, Netherlands as value
    from tableB
    -- unpivot end
) as B
    on A.country = B.country
    and A.sku = B.sku
set A.pricecountry = B.value;

But since mySQL doesn't have an UNPIVOT function and you need to do it manually using UNION ALL, you should consider changing your table (tableB) structure.

Test it here

score 1 · Answer 2 · answered Dec 19 '17 at 12:16

UPDATE
    a
INNER JOIN
    b
        ON a.sku = b.sku
SET
    a.price = CASE a.country
                WHEN 'germany'     THEN b.germany
                WHEN 'france'      THEN b.france
                WHEN 'netherlands' THEN b.netherlands END
WHERE
    a.price IS NULL
;

http://sqlfiddle.com/#!9/6485d/1

score 0 · Answer 3 · answered Dec 19 '17 at 12:12

Doing it for all columns:-

UPDATE tableA a 
LEFT OUTER JOIN 
(
    SELECT sku, 'Germany' AS country, Germany AS pricecountry
    FROM tableB
    UNION
    SELECT sku, 'France', France
    FROM tableB
    UNION
    SELECT sku, 'Netherlands', Netherlands
    FROM tableB
) b
ON a.sku = b.sku
AND a.country = b.country
SET a.pricecountry = b.pricecountry

But you would need to amend this whenever a new country column was added

score 0 · Answer 4 · answered Dec 19 '17 at 12:16

The best performance is probably to use CASE logic:

UPDATE tableA a JOIN
       tableB b
       ON a.sku = b.sku
    SET a.pricecountry = (CASE WHEN a.country = 'Netherlands' THEN b.Netherlands
                               WHEN a.country = 'Germany' THEN b.Germany
                               WHEN a.country = 'France' THEN b.France
                          END)
    WHERE a.pricecountry IS NULL;

Although unpivoting TableB is reasonable, it prevents the use of an index on sku -- which is why it is bad for performance.

Note that TableB has a poor data structure. Instead of having one column per country, it should have one row per country.

I suspect the poor structure is why the OP is asking the question. The desired structure is the one the OP appears to be attempting to create. — MatBailie, Dec 19 '17 at 12:18

score 0 · Answer 5 · answered Dec 19 '17 at 12:48

I've done it in MSSQL:

update a
set a.pricecountry = (case when a.country = 'Netherlands' then b.Netherlands 
                       when a.country = 'Germany' then b.Germany 
                       when a.country = 'France' then b.France end)
from tableA a
inner join tableB b
on a.sku = b.sku
where a.pricecountry is null;

Update table A with info of B with value as a column name

5 Answers5