locked
Help required for update SQL RRS feed

  • Question

  • Hi Guys,
    I am looking for some help to frame the update query.
    I have two tables temp_1 and temp_2.

    I want to update temp_2 based on data from temp_1. However i do not want to make an update where temp_1 has null value.
    One approach i can think of is creating two different update statements but is there any chance to get it done through
    only one statement. Also i would like to add here that temP_1 has 250K data and i going to make update on approx 500K
    rows from temp_2. So i need to look from performance point of view as well.

    Temp_1                       temp_2
    Col1   Col2  Col3        col1     col2    col3
    1 abc   abc                  1        null    null
    2 abc   null                  2         xyz     xyz
    3 null  null                   3          xyz     null

    After update i am looking forward to get data in below format.

    temp_2
    col1     col2    col3
    1        abc     abc
    2        abc     xyz
    3        xyz     null  

    Wednesday, April 16, 2014 3:29 PM

Answers

  • USE TESTdb
    go
    create table express (col1 int, col2 varchar(25), col3 varchar(25))
    go 
    create table express2 (col1 int, col2 varchar(25), col3 varchar(25))
    go
    insert into express values (1,'abc','abc'),(2,'abc',null),(3,null,null)
    insert into express2 values (1,null,null),(2,'xyz','xyz'),(3,'xyz',null)
    
    select * from express
    select * from express2
    
    update express2
    set col2= case when x.col2 is null and e.col2 is not null then e.col2 
    				else x.col2
    				end ,
    col3= case when x.col3 is null and e.col3 is not null then e.col3 
    		else x.col3
    	end 
    from express e inner join express2 x on e.col1=x.col1 
    
    --drop table express, express2


    ebro

    • Marked as answer by JDK21 Wednesday, April 16, 2014 4:08 PM
    Wednesday, April 16, 2014 3:57 PM