Post reply

Update all fields in a record except incoming NULL values?

  • July 29, 2016 at 12:35 pm

    #328580

    I need to setup an SP to update a record in my table, however any incoming data my SP uses to make the update that is NULL, should be ignored. What's the best way to go about doing this?

    So just to clarify with an example, I have a table called Table1 with one record:

    Table1_Id | Field1 | Field 2 | Field3

    123 | qrs | tuv | wxyz

    I'm passing these values into my SP:

    ID | Value1 | Value 2 | Value3

    123 | abc | NULL | def

    Since Value2 is NULL, Table1.Field2's new value should stay the same. Here's my new table:

    Table1_Id | Field1 | Field 2 | Field3

    123 | abc | tuv | def

    What's the best way to handle this?

    Thanks

  • July 29, 2016 at 12:39 pm

    #1892707

    probably with a CASE statement;

    something like this in your proc(assuming parameters are available for all four fields you mentione:

    update Table1

    SET Field1 = CASE WHEN @Field1 IS NULL THEN Field1 ELSE @Field1 END,

    Field2 = CASE WHEN @Field2 IS NULL THEN Field2 ELSE @Field2 END,

    Field3 = CASE WHEN @Field3 IS NULL THEN Field3 ELSE @Field3 END,

    WHERE Table1_Id = @Table1_Id

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 29, 2016 at 12:54 pm

    #1892711

    Since Lowell already posted something, I'll just post the short version.

    update Table1

    SET Field1 = ISNULL( @Field1, Field1),

    Field2 = ISNULL( @Field2, Field2),

    Field3 = ISNULL( @Field3, Field3)

    WHERE Table1_Id = @Table1_Id;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 2 (of 2 total)

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