none
An Update query which update the data only if null is the value in the column else ignore that value. RRS feed

  • Question

  • 'I want update statement:

    An Update query which update the data only if null is the value in the column else ignore that value.

    Primary key is there,


    Thanks

    Thursday, August 30, 2012 4:07 PM

Answers

  • Try one of these:

    Update	x
    Set		x.Col = Case
    					When x.Col Is Null Then y.Value
    					Else x.Col
    				End
    From	myTable x
    join	otherTable y
    	on	x.someID = y.someID

    Simpler:

    Update	x
    Set		x.Col = Case
    					When x.Col Is Null Then 'Value'
    					Else x.Col
    				End
    From	myTable x

    • Edited by dgjohnson Thursday, August 30, 2012 4:14 PM forums are silly
    • Proposed as answer by Roberson Ferreira _ Friday, August 31, 2012 12:52 PM
    • Marked as answer by Ajay.G Friday, September 14, 2012 10:48 AM
    Thursday, August 30, 2012 4:13 PM
  • If you only need to update one column, then you can use this:

    UPDATE my_table
    SET my_column = @value
    WHERE my_column IS NULL
    
    Otherwise, use one of the other suggestions.


    Gert-Jan

    Thursday, August 30, 2012 8:35 PM
  • Do like this
    update tab1
    set col1=b.col2
    from tab1 a
    join db2.dbo.tab2 b
    on a.key1=b.key2
    where col1 is null;

    Many Thanks & Best Regards, Hua Min

    Friday, August 31, 2012 5:23 AM

All replies

  • Try one of these:

    Update	x
    Set		x.Col = Case
    					When x.Col Is Null Then y.Value
    					Else x.Col
    				End
    From	myTable x
    join	otherTable y
    	on	x.someID = y.someID

    Simpler:

    Update	x
    Set		x.Col = Case
    					When x.Col Is Null Then 'Value'
    					Else x.Col
    				End
    From	myTable x

    • Edited by dgjohnson Thursday, August 30, 2012 4:14 PM forums are silly
    • Proposed as answer by Roberson Ferreira _ Friday, August 31, 2012 12:52 PM
    • Marked as answer by Ajay.G Friday, September 14, 2012 10:48 AM
    Thursday, August 30, 2012 4:13 PM
  • UPDATE yourtable
    SET col = ISNULL(col, @col)
    --WHERE condition

    • Proposed as answer by Gert-Jan Strik Thursday, August 30, 2012 8:34 PM
    Thursday, August 30, 2012 4:18 PM
    Moderator
  • If you only need to update one column, then you can use this:

    UPDATE my_table
    SET my_column = @value
    WHERE my_column IS NULL
    
    Otherwise, use one of the other suggestions.


    Gert-Jan

    Thursday, August 30, 2012 8:35 PM
  • These two tables are from two different database and I need to import data from one table to other and checked based on primary key.

    Should I use join ?


    Thanks

    Friday, August 31, 2012 5:06 AM
  • Yes you need to join the table and do it unless you are not using any third party tools.

    If the two DBs are in the same server, you can use <dbName>.<tablename>.

    If those are in different servers, create Linked server between the servers and use 4 part format to join the tables.<Servername>.<dbName>.<Schema>.<tableName>


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, August 31, 2012 5:19 AM
  • Do like this
    update tab1
    set col1=b.col2
    from tab1 a
    join db2.dbo.tab2 b
    on a.key1=b.key2
    where col1 is null;

    Many Thanks & Best Regards, Hua Min

    Friday, August 31, 2012 5:23 AM
  • Hi Huamin,

      Update test.dbo.table1
     set test.dbo.table1.id2=test2.dbo.table1.id2;
     from test.dbo.table1 
    join test2.dbo.table1
     on test.dbo.table1.id1=test2.dbo.table1.id1
     where test.dbo.table1.id2 is null;

    getting error:

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'from'.

    Thanks

    Thursday, September 13, 2012 10:45 AM
  • Hi

    just remove the ;

    and

    try this query

    Update test.dbo.table1
     set id2=test2.dbo.table1.id2
     from test.dbo.table1  
    join test2.dbo.table1
     on test.dbo.table1.id1=test2.dbo.table1.id1
     where test.dbo.table1.id2 is null

    thanks

    ravilla

    • Edited by RRavilla Thursday, September 13, 2012 11:42 AM
    • Proposed as answer by RRavilla Friday, September 14, 2012 10:47 AM
    Thursday, September 13, 2012 11:40 AM