Updating multiple columns in a table based on two columns in another table

Question

I am trying to update a table (Table1) that has multiple columns..

id     Engine      Transmission     Exhaust      Axle
--     -------       -------        --------    ------
1        CC              NX1           BP1       F1
2        C1              NX2           BP2       F2
3        C2              NX3           BP3       F3

I want to update this table using Name and Value in Table2...

id     Name                Value     
--     -------            -------       
1       Engine              C4          
2       Transmission        NX4          
3       Exhaust             BP5         
4       Axle                 F4

Joining the two tables is easy based on just ID. But is it possible to 'dynamically' update the columns in Table1 with Table2.Value using Table2.Name. Some how using Table2.Name to know which column in Table1 will be updated with Table2.Value?

So far I've only been able to think of a basic SELECT to pull the data I am looking for based on a WHERE EXISTS:

SELECT * FROM table1 t1
Where exists (Select NULL
           from scpomgr.table2 t2
           WHERE t1.id = t2.id);

The results would be table1:

id     Engine      Transmission     Exhaust      Axle
--     -------       -------        --------    ------
1        C4              NX1           BP1       F1
2        C1              NX4           BP2       F2
3        C2              NX3           BP5       F3
4        C3              NX5           BP4       F4

Can you show us what you've done so far? Are you sure that we can join two table by ID column? — Roman Marusyk, Nov 17 '15 at 21:42
Possible duplicate of [Oracle - Update statement with inner join](http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join) — FuzzyTree, Nov 17 '15 at 21:44
I don't see it being a copy of the Update Statement with inner Join. As this query you would need to Update Table1. Engine, Table1.Transmission, Table1.Exhaust, and Table1.Axle: based on the Name in Table2.Name. — user3208073, Nov 17 '15 at 21:53

score 0 · Accepted Answer · answered Nov 18 '15 at 13:42

You could first try to pivot Table2 so that it would have same columns as Table1, then perform normal update with joins. By the way - are you sure that you can join both tables by Id? In my solution I added a foreign key to Table1 (Table1Id).

My solution is for Sql Server, but the logic should be the same for Oracle.

with t2Pivot as (
    select Table1Id, Engine, Transmission, Exhaust, Axle
    from (
        select Table1Id, Name, Value
        from Table2
    ) as s 
    pivot (
        min(Value) for Name in (Engine, Transmission, Exhaust, Axle)
    ) as p
)
update t1 set
    Engine = isnull(t2.Engine, t1.Engine),
    Transmission = isnull(t2.Transmission, t1.Transmission),
    Exhaust = isnull(t2.Exhaust, t1.Exhaust),
    Axle = isnull(t2.Axle, t1.Axle)
from Table1 t1 left join t2Pivot t2 on (t1.Id = t2.Table1Id)

sqlfiddle

Updating multiple columns in a table based on two columns in another table

1 Answers1