none
Update table a columns using columns from table b (values of 2 columns of table b need to taken from table c) RRS feed

  • Question

  •  

    Guys,
    I need to update table A columns col3, col4, col5 and col6 by table b columns col3, col4, col5 and col6 however table b col5 and col6 values need to come from table c col1.
    Means table b col5 and col6 have values in it however i need to replace them with value from table c col1 and need to update table a col5 and col6 accordingly.
    table a and table b has col1 and col2 in common.

    i am trying something like this.

    Update a
    a.col3 = b.col3,
    a.col4 = b.col4,
    a.col5 = (select col1 from table_c c where c.col2=b.col5),
    a.col6 = (select col1 from table_c c where c.col2=b.col6)
    from table_A a inner join table_b
    on  a.col1=b.col1 and a.col2=b.col2

    can someone help me reframe above update query?
    thanks in advance for your help.

    Monday, March 31, 2014 1:02 PM

Answers

  • Try the below:(If you have multiple values, then you may need to use TOP 1 as commented code in the below script)

    create Table tableA(Col1 int,Col2 int,Col3 int,Col4 int,Col5 int,Col6 int)
    
    Insert into tableA values(1,2,3,4,5,6)
    
    create Table tableB(Col1 int,Col2 int,Col3 int,Col4 int,Col5 int,Col6 int)
    
    Insert into tableB values(1,2,30,40,50,60)
    
    create Table tableC(Col1 int,Col2 int,Col3 int,Col4 int,Col5 int,Col6 int)
    
    Insert into tableC values(100,50,30,40,2,2)
    --Insert into tableC values(200,50,30,40,2,2)
    Insert into tableC values(100,60,30,40,2,2)
    
    Select * From tablea
    
    Update a Set
    a.col3 = b.col3,
    a.col4 = b.col4,
    a.col5 = (select  col1 from tablec c where c.col2=b.col5 ),
    a.col6 = (select  col1 from tablec c where c.col2=b.col6 )
    from tableA a inner join tableb b
    on  a.col1=b.col1 and a.col2=b.col2
    
    --Update a Set
    --a.col3 = b.col3,
    --a.col4 = b.col4,
    --a.col5 = (select Top 1 col1 from tablec c where c.col2=b.col5 Order by c.Col1 asc),
    --a.col6 = (select Top 1 col1 from tablec c where c.col2=b.col6  Order by c.Col1 asc)
    --from tableA a inner join tableb b
    --on  a.col1=b.col1 and a.col2=b.col2
    
    Select * From tablea
    
    Drop table tablea,Tableb,TableC

    • Marked as answer by JDK21 Monday, March 31, 2014 2:37 PM
    Monday, March 31, 2014 1:16 PM

All replies

  • JDK,

    Check this:

    UPDATE a
    	a.col3 = b.col3,
    	a.col4 = b.col4,
    	a.col5 = c1.col1,
    	a.col6 = c2.col1
    FROM table_A a 
    INNER JOIN table_B b
    	ON  a.col1=b.col1 AND a.col2=b.col2
    LEFT JOIN table_C c1 ON c1.col2=b.col5
    LEFT JOIN table_C c2 ON c2.col2=b.col6


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Monday, March 31, 2014 1:14 PM
  • Try the below:(If you have multiple values, then you may need to use TOP 1 as commented code in the below script)

    create Table tableA(Col1 int,Col2 int,Col3 int,Col4 int,Col5 int,Col6 int)
    
    Insert into tableA values(1,2,3,4,5,6)
    
    create Table tableB(Col1 int,Col2 int,Col3 int,Col4 int,Col5 int,Col6 int)
    
    Insert into tableB values(1,2,30,40,50,60)
    
    create Table tableC(Col1 int,Col2 int,Col3 int,Col4 int,Col5 int,Col6 int)
    
    Insert into tableC values(100,50,30,40,2,2)
    --Insert into tableC values(200,50,30,40,2,2)
    Insert into tableC values(100,60,30,40,2,2)
    
    Select * From tablea
    
    Update a Set
    a.col3 = b.col3,
    a.col4 = b.col4,
    a.col5 = (select  col1 from tablec c where c.col2=b.col5 ),
    a.col6 = (select  col1 from tablec c where c.col2=b.col6 )
    from tableA a inner join tableb b
    on  a.col1=b.col1 and a.col2=b.col2
    
    --Update a Set
    --a.col3 = b.col3,
    --a.col4 = b.col4,
    --a.col5 = (select Top 1 col1 from tablec c where c.col2=b.col5 Order by c.Col1 asc),
    --a.col6 = (select Top 1 col1 from tablec c where c.col2=b.col6  Order by c.Col1 asc)
    --from tableA a inner join tableb b
    --on  a.col1=b.col1 and a.col2=b.col2
    
    Select * From tablea
    
    Drop table tablea,Tableb,TableC

    • Marked as answer by JDK21 Monday, March 31, 2014 2:37 PM
    Monday, March 31, 2014 1:16 PM
  • Small correction in Jay's Code:

    UPDATE a Set
    	a.col3 = b.col3,
    	a.col4 = b.col4,
    	a.col5 = c1.col1,
    	a.col6 = c2.col1
    FROM tableA a 
    INNER JOIN tableB b
    	ON  a.col1=b.col1 AND a.col2=b.col2
    LEFT JOIN tableC c1 ON c1.col2=b.col5
    LEFT JOIN tableC c2 ON c2.col2=b.col6

    Monday, March 31, 2014 1:19 PM
  • Merge table_A a 
    Using(
    Select  b.col1, b.col2, b.col3, b.col4, c1.col1 as col5, c2.col1 as col6
    from tableb b 
    left join table_c c1 On c1.col2=b.col5
    left join table_c c2 On c2.col2=b.colb )  src On a.col1=src.col1 and a.col2=src.col2
    When Matched Then
    Update
     Set
    col3 = src.col3,
    col4 = src.col4,
    col5 = src.col5,
    col6 = src.col6
    ;

    Monday, March 31, 2014 1:23 PM
    Moderator
  • Do not use the old proprietary Sybase UPDATE FROM syntax. It does not work! Google it. Without DDL or sample data, her is a guess. 

    MERGE INTO Alpha AS A
    USING (SELECT B.col1, B.col2, B.col3,B.col4,
                  C1.col1 AS col5, C2.col1 AS col6
             FROM Beta AS B, Gamma AS C1, Gamma AS C2
            WHERE C1.col2 = B.col5
              AND C2.col2 = B.col6)
           AS X(col1, col2, col3, col4, col5, col6)
    ON A.col1 = X.col1 
       AND A.col2 = X.col2
    WHEN MATCHED
    THEN UPDATE
         SET col3 = X.col3,
             col4 = X.col4,
             col5 = X.col5,
             col6 = X.col6;

    No data given, so it is untested.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 31, 2014 2:13 PM
  • Folks thanks for your help.

    Left Join is giving error but above code working absolutely fine when i used inner join instead of Left Join.

    • Edited by JDK21 Monday, March 31, 2014 2:37 PM
    Monday, March 31, 2014 2:19 PM