Updating the main table row using another table which have multiple rows in sql server

Question

I have 2 tables with common column id

Table A:
ID   value1    value2    value3
------------------------------------        
 1     0         0        0

Table B:
ID    value_type      value   
--------------------------------------          
1     value1          0.01  
2     value2          0.02
3     value3          0.03

I want to update table A value using table b value,

Final Output :

ID   value1    value2    value3
-----------------------------------------
1     0.01       0.02      0.03

Can anyone show me how to write a query and fetch the result as above, Thanks very much! I have tried join, pivot. all result nothing.

Might be ID column having value 1 for all records of Table B in given example. Please confirm. — NP007
– NP007, Commented Oct 8, 2018 at 6:17
yes there is multiple value in table A as well as table B with common column ID — The beginner
– The beginner, Commented Oct 8, 2018 at 6:19
Not according to the sample data you've posted. In table A you only posted one record and it table B every record have a different value in the ID column. Please edit your question to resolve the conflict between the text and the sample data. While you're at it, you should read the excellent post over on meta.dba.stackexchange called Help me write this query in SQL. and convert the sample data to DDL+DML statements. — Zohar Peled
– Zohar Peled, Commented Oct 8, 2018 at 6:27

Zohar Peled · Accepted Answer · 2018-10-08 06:17:58Z

1

A quick (but not scalable) solution would be to use an update statement with a join to a derived table with conditional aggrigation:

UPDATE T
SET value1 = v1,
    value2 = v2,
    value3 = v3
FROM TableA T
JOIN 
(
    SELECT 1 As ID
           MAX(CASE WHEN value_type = 'value1' THEN value END) As v1,
           MAX(CASE WHEN value_type = 'value2' THEN value END) As v2,
           MAX(CASE WHEN value_type = 'value3' THEN value END) As v3
    FROM TableB
) V
    ON T.ID = V.ID

answered Oct 8, 2018 at 6:17

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

The beginner Over a year ago

there are no fixed column numbers in table it is possible to fetch dynamic?

Zohar Peled Over a year ago

Please edit your question to specify your needs exactly. Also, be sure that the sample data matches the text. Before you do that, anything I'll write would be a guess.

Gordon Linoff Over a year ago

@Thebeginner . . . This answers the question that you have asked, which is specifically about updating three columns in the first table. If you have a different question, then ask it as a new question. You should be aware that tables do not have dynamic columns, so be careful about how you phrase your next question.

Chanukya · Accepted Answer · 2018-10-08 07:13:40Z

0

using pivot we can do this

Since iam storing data into one table and after that only iam doinn updation

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.value_type)
            FROM #b c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT rn as id , '  + (@cols) + ' into dynamictable  from 
            (
                select *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID ) AS RN
                from #b
           ) x
            pivot 
            (
                MAX(VALUEE)
                for VALUE_TYPE in (' + @cols + ')
            ) p 
          '


exec(@query)
UPDATE T
SET T.value1 = V.value1,
    T.value2 = V.value2,
    T.value3 = V.value3
FROM #a T
JOIN 
(SELECT   id ,MAX(VALUE1)VALUE1 , MAX(VALUE2)VALUE2, MAX(VALUE3)VALUE3 
FROM  dynamictable
GROUP BY id
) V
    ON T.ID = V.ID

edited Oct 8, 2018 at 7:13

answered Oct 8, 2018 at 6:30

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

4 Comments

The beginner Over a year ago

there are no fixed column numbers in table it is possible to fetch dynamic?

Chanukya Over a year ago

@Thebeginner then you have to use dynamic pivot

The beginner Over a year ago

can u please help me?

Chanukya Over a year ago

@Thebeginner this will not be possible even dynamic query also because we can do upto pivot multiple columns but the thing is we cant update the required output into table1 since table is having only 3 columns so i can give code upto dynamic pivot only

Collectives™ on Stack Overflow

Updating the main table row using another table which have multiple rows in sql server

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related