Update Query

  • Hi All,

    I need help on updating following tables. Please suggest best way in terms of performance prospective.

    Table1:

    ROW ID1 ID2 ID3 ID4 ID5 ID6

    1 NULL NULL NULL NULL NULLNULL

    Table2:

    ID Value

    110

    220

    330

    412

    532

    612

    I want to update table1 from table2. After update the data should be like below:

    Table1:

    ROW ID1 ID2 ID3 ID4 ID5 ID6

    1 10 20 30 12 32 12

    Currently I wrote 6 queries for acheving this. Please refer the below queries:

    UPDATE t1 set t1.ID1 = t2.value

    from Table1 t1

    inner join Table2 t on t2.ID =1

    UPDATE t1 set t1.ID2 = t2.value

    from Table1 t1

    inner join Table2 t on t2.ID =2

    UPDATE t1 set t1.ID3 = t2.value

    from Table1 t1

    inner join Table2 t on t2.ID =3

    UPDATE t1 set t1.ID4 = t2.value

    from Table1 t1

    inner join Table2 t on t2.ID =4

    UPDATE t1 set t1.ID5 = t2.value

    from Table1 t1

    inner join Table2 t on t2.ID =5

    UPDATE t1 set t1.ID6 = t2.value

    from Table1 t1

    inner join Table2 t on t2.ID =6

    Please suggest me is there any best way.

    Thanks in advance

    Vijay

  • having a quick look I can't see how your update queries will work but that said:

    If there is more than one entry in Table 1 how do we identify what we should be using from table 2 to update in table 1?

  • I am sorry for that. Actually there is common column is there in two tables. Please check below

    Table1:

    PID ID1 ID2 ID3 ID4 ID5 ID6

    1 NULL NULL NULL NULL NULL NULL

    2 NULL NULL NULL NULL NULL NULL

    3 NULL NULL NULL NULL NULL NULL

    Table2:

    PID ID Value

    1 1 10

    1 2 20

    1 3 30

    1 4 12

    1 5 32

    1 6 12

    2 1 11

    2 2 21

    2 3 30

    2 4 15

    2 5 32

    2 6 12

    3 1 18

    3 2 20

    3 3 33

    3 4 12

    3 5 42

    3 6 52

    Now my query is:

    update t1 set t1.ID1 = t2.value

    from Table1 t1

    inner join Table2 t on t1.PID = t2.PID AND t2.ID=1

    update t1 set t1.ID2 = t2.value

    from Table1 t1

    inner join Table2 t on t1.PID = t2.PID AND t2.ID=2

    .

    .

    .

    .

    Thanks in advance,

    Vijay

  • Not sure about the performance but tried doing it following way... please consider cte and cte1 as tables wrt to update statement written at the end

    ;with cte as

    (

    select 1 as pid, null as id1, null as id2, null as id3

    union all

    select 2 as pid, null as id1, null as id2, null as id3

    ),

    cte1 as

    (

    select 1 as pid,1 as id,10 as value

    union all

    select 1 as pid,2 as id,20 as value

    union all

    select 1 as pid,3 as id,30 as value

    union all

    select 2 as pid,1 as id,10 as value

    union all

    select 2 as pid,2 as id,20 as value

    union all

    select 2 as pid,3 as id,30 as value

    ),

    cte2 as

    (

    select distinct pid,

    (select value from cte1 c1 where c1.pid = c2.pid and c1.id = 1 ) as id1,

    (select value from cte1 c1 where c1.pid = c2.pid and c1.id = 2 ) as id2,

    (select value from cte1 c1 where c1.pid = c2.pid and c1.id = 3 ) as id3 from cte1 c2

    )

    update cte

    set id1 = c2.id1,id2 = c2.id2,id3 = c2.id3

    from cte2 c2 inner join cte c on c2.pid = c.pid

  • Please check the SQL snippet below and also the attached image for before and after snapshots of the data. I hope it helps.

    -- Table1 has just MyID...rest of the columns all have NULL values

    DECLARE @Table1 TABLE (MyID INT, ID1 INT, ID2 INT, ID3 INT, ID4 INT)

    INSERT @Table1 (MyID, ID1, ID2, ID3, ID4) VALUES (1, NULL, NULL, NULL, NULL), (2, NULL, NULL, NULL, NULL), (3, NULL, NULL, NULL, NULL), (4, NULL, NULL, NULL, NULL)

    SELECT * FROM @Table1

    -- Table2 has multiple values for MyID; each MyID has multiple ColumnNames (a max of 4, from ID1 thru ID4) and corresponding ColumnValues

    DECLARE @Table2 TABLE (MyID INT, ColumnName VARCHAR (10), ColumnValue INT)

    INSERT @Table2 VALUES (1, 'ID1', 10), (1, 'ID2', 20), (1, 'ID3', 30), (1, 'ID4', 40), (2, 'ID1', 101), (2, 'ID2', 102), (2, 'ID3', 103), (3, 'ID1', 1001), (3, 'ID3', 1003), (4, 'ID1', 111), (4, 'ID2', 222), (4, 'ID3', 333), (4, 'ID4', 444)

    SELECT * FROM @Table2

    -- Update Table1 by PIVOTing Table2 to match the structure

    UPDATE@Table1

    SETt1.ID1 = pvt.ID1,

    t1.ID2 = pvt.ID2,

    t1.ID3 = pvt.ID3,

    t1.ID4 = pvt.ID4

    FROM@Table1 t1

    INNER JOIN (

    SELECT*

    FROM@Table2

    PIVOT

    (AVG(ColumnValue) FOR ColumnName IN ([ID1], [ID2], [ID3], [ID4])

    ) AS MyVal

    ) pvt

    ON t1.MyID = pvt.MyID

    SELECT * FROM @Table1

    - RexHelios

  • vijaykumar587 (3/7/2014)


    I am sorry for that. Actually there is common column is there in two tables. Please check below

    Table1:

    PID ID1 ID2 ID3 ID4 ID5 ID6

    1 NULL NULL NULL NULL NULL NULL

    2 NULL NULL NULL NULL NULL NULL

    3 NULL NULL NULL NULL NULL NULL

    Table2:

    PID ID Value

    1 1 10

    1 2 20

    1 3 30

    1 4 12

    1 5 32

    1 6 12

    2 1 11

    2 2 21

    2 3 30

    2 4 15

    2 5 32

    2 6 12

    3 1 18

    3 2 20

    3 3 33

    3 4 12

    3 5 42

    3 6 52

    Now my query is:

    update t1 set t1.ID1 = t2.value

    from Table1 t1

    inner join Table2 t on t1.PID = t2.PID AND t2.ID=1

    update t1 set t1.ID2 = t2.value

    from Table1 t1

    inner join Table2 t on t1.PID = t2.PID AND t2.ID=2

    .

    .

    .

    .

    Thanks in advance,

    Vijay

    You'll have much better luck getting answers in the future if you make your tables and sample data in a readily consumable manner instead of just posting plain text like you did. Please see the first article under "Helpful Links" in my signature line below for a method to do that properly.

    Since you're a newbie, I'll do it for you this time. The following creates the test data you gave in a test table and then offers a high performance CROSSTAB as a solution to not only populate Table 1, but create it, as well. You shouldn't need an update for this.

    --===== Build and populate the test table on the fly.

    -- This is NOT a part of the solution. We' just

    -- building some test data here.

    SELECT PID,ID,[Value]

    INTO #Table2

    FROM (--==== Test data

    SELECT 1,1,10 UNION ALL

    SELECT 1,2,20 UNION ALL

    SELECT 1,3,30 UNION ALL

    SELECT 1,4,12 UNION ALL

    SELECT 1,5,32 UNION ALL

    SELECT 1,6,12 UNION ALL

    SELECT 2,1,11 UNION ALL

    SELECT 2,2,21 UNION ALL

    SELECT 2,3,30 UNION ALL

    SELECT 2,4,15 UNION ALL

    SELECT 2,5,32 UNION ALL

    SELECT 2,6,12 UNION ALL

    SELECT 3,1,18 UNION ALL

    SELECT 3,2,20 UNION ALL

    SELECT 3,3,33 UNION ALL

    SELECT 3,4,12 UNION ALL

    SELECT 3,5,42 UNION ALL

    SELECT 3,6,52

    )d(PID,ID,[Value])

    ;

    --===== Pivot the data using the ancient high performance

    -- method of a "CROSSTAB" to build Table 1.

    SELECT PID

    ,ID1 = SUM(CASE WHEN ID=1 THEN [Value] ELSE 0 END)

    ,ID2 = SUM(CASE WHEN ID=2 THEN [Value] ELSE 0 END)

    ,ID3 = SUM(CASE WHEN ID=3 THEN [Value] ELSE 0 END)

    ,ID4 = SUM(CASE WHEN ID=4 THEN [Value] ELSE 0 END)

    ,ID5 = SUM(CASE WHEN ID=5 THEN [Value] ELSE 0 END)

    ,ID6 = SUM(CASE WHEN ID=6 THEN [Value] ELSE 0 END)

    INTO #Table1

    FROM #Table2

    GROUP BY PID

    ;

    --===== Display the content of the new Table1

    SELECT *

    FROM #Table1

    ;

    Results:

    PID ID1 ID2 ID3 ID4 ID5 ID6

    ----------- ----------- ----------- ----------- ----------- ----------- -----------

    1 10 20 30 12 32 12

    2 11 21 30 15 32 12

    3 18 20 33 12 42 52

    (3 row(s) affected)

    If it absolutely has to be done as an update, then just use the CROSSTAB as a CTE and join to it for the update.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply