update columns in a table based on row values from another table

Question

I have a table TABLE1 with data in this format:

    id     text       pct
    --     ---      -----
    1        AA        5
    1        BB        3
    1        CC        16
    2        BB        7
    3        CC        13

And another table TABLE2:

    id     columnAA      columnBB     columnCC
    --     -------       -------      --------
    1        0             0             0
    2        0             0             0
    3        0             0             0

I would like to update columns in table2 so my result will look like:

    id     columnAA      columnBB     columnCC 
    --     -------       -------      -------- 
    1        5              3             16 
    2        0              7              0 
    3        0              0             13 

I tried with this code but it's not really working. It only updates one column!

    update a
      set columnAA = case when b.text = 'AA' then b.pct else columnAA end
     ,set columnBB = case when b.text = 'BB' then b.pct else columnBB end
     ,set columnCC = case when b.text = 'CC' then b.pct else columnCC end
    from table2 a
    join table1 b
      on a.id = b.id

Answer 1

As Tab Allerman stated, You could do something like this:

UPDATE TABLE2
SET columnAA = (SELECT TABLE1.pct FROM TABLE1 WHERE TABLE1.text = 'AA'AND TABLE2.id = TABLE1.id),
columnBB  = (SELECT TABLE1.pct FROM TABLE1 WHERE TABLE1.text = 'BB'AND TABLE2.id = TABLE1.id),
columnCC  = (SELECT TABLE1.pct FROM TABLE1 WHERE TABLE1.text = 'CC'AND TABLE2.id = TABLE1.id)

edit:

In looking more closely at your code. Remove the second and third 'Set' and it should work. You do not need to proceed each with the word 'Set' only the first.