How to UPDATE pivoted table in SQL SERVER

Question

I have flat table which I have to join using EAN attribute with my main table and update gid (id of my main table).

id     attrib    value        gid
1      weight    10           NULL
1      ean       123123123112 NULL
1      color     blue         NULL
2      weight    5            NULL
2      ean       331231313123 NULL

I was trying to pivot ean rows into column, next join on ean both tables, and for this moment everything works great.

--update SideTable
--set gid = ab_id
select gid, ab_id
  from SideTable
  pivot (max (value) for attrib in ([EAN],[MPN])) as b
  join MainTable as c
  on c.ab_ean = b.EAN
  where b.EAN !='' AND c.ab_archive = '0'

When I am selecting both id columns is okey, but when I am uncomment first lines and delete select whole table is set with first gid from my main table.

It have to set my main id into all attributes where ID where ean is matched from my main table.

I am sorry for my terrible english but I hope someone can help me, with that.

possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Maciej Los, Aug 13 '15 at 10:46
Maciej is actually right, I used that duplicate answer to help give you a specific answer. — hamish, Aug 13 '15 at 10:52
Both answers return same effect, whole table is set with 1 gid — tomipnh, Aug 13 '15 at 11:07

score 2 · Accepted Answer · answered Aug 13 '15 at 11:31

The reason your update does not work is that you don't have any link between your source and target for the update, although you reference sidetable in the FROM clause, this is effectively destroyed by the PIVOT function, leaving no link back to the instance of SideTable that you are updating. Since there is no link, all rows are updated with the same value, this will be the last value encountered in the FROM.

This can be demonstrated by running the following:

DECLARE @S TABLE (ID INT, Attrib VARCHAR(50), Value VARCHAR(50), gid INT);
INSERT @S 
VALUES 
    (1, 'weight', '10', NULL), (1, 'ean', '123123123112', NULL), (1, 'color', 'blue', NULL),
    (2, 'weight', '5', NULL), (2, 'ean', '331231313123', NULL);

SELECT  s.*
FROM    @S AS s
        PIVOT (MAX(Value) FOR attrib IN ([EAN],[MPN])) AS pvt;

You clearly have a table aliased s in the FROM clause, however because you have used pivot you cannot use SELECT s*, you get the following error:

The column prefix 's' does not match with a table name or alias name used in the query.

You haven't provided sample data for your main table, but I am about 95% certain your PIVOT is not needed, I think you can get your update using just normal JOINs:

UPDATE  s
SET     gid = ab_id
FROM    SideTable AS s
        INNER JOIN SideTable AS ean
            ON ean.ID = s.ID
            AND ean.attrib = 'ean'
        INNER JOIN MainTable AS m
            ON m.ab_EAN = ean.Value
WHERE   m.ab_archive = '0'
AND     m.ab_EAN != '';

Thanks for answer I wrote cursor almost same, after throwing out pivot it started to work — tomipnh, Aug 13 '15 at 17:17

hamish · Answer 2 · 2015-08-13T10:56:24.023

0

try and break it down a bit more like this..

update SideTable
set SideTable.gid = p.ab_id
FROM
  (
   select gid, ab_id
   from SideTable
   pivot (max (value) for attrib in ([EAN],[MPN])) as b
   join MainTable as c
   on c.ab_ean = b.EAN
   where b.EAN !='' AND c.ab_archive = '0'
  ) p
WHERE p.EAN = SideTable.EAN

edited Aug 13 '15 at 10:56

answered Aug 13 '15 at 10:51

hamish

1,141
1
12
21

score 0 · Answer 3 · answered Aug 13 '15 at 10:52

As per comment to the question, you need to use update + select statement.

A standard version looks like:

UPDATE
    T
SET
    T.col1 = OT.col1,
    T.col2 = OT.col2
FROM
    Some_Table T
INNER JOIN
    Other_Table OT
ON
    T.id = OT.id
WHERE
    T.col3 = 'cool'

As to your needs:

update a
set a.gid = p.ab_id
from SideTable As a 
Inner join (
select gid, ab_id
  from SideTable
  pivot (max (value) for attrib in ([EAN],[MPN])) as b
  join MainTable as c
  on c.ab_ean = b.EAN
  where b.EAN !='' AND c.ab_archive = '0') p ON a.ean = p.EAN

How to UPDATE pivoted table in SQL SERVER

3 Answers3