Post reply

update statements

  • February 8, 2012 at 12:31 pm

    #391516

    I have a table mytable that has columns like this:

    A-ID, A-Name, B-ID, B-Name, C-ID, C-Name, D-ID, D-anme

    I would do an update query to clean like this: for each ID, if it is null, then set A-Name to null.

    I know I can do something like this:

    Update mytable

    set A-Name =null

    where A-ID =Null.

    Then

    update mytable

    set B-Name=Null

    where B-ID=Null

    ....

    But I need to write quite a few updates statement,

    is there a way to combine them together to just use one update statement

    Thanks

  • February 8, 2012 at 12:40 pm

    #1444727

    Try this:

    UPDATE mytable SET A-NAME = CASE WHEN A-ID IS NULL THEN NULL ELSE A-NAME end,

    B-NAME = CASE WHEN B-ID IS NULL THEN NULL ELSE B-NAME end,

    C-NAME = CASE WHEN C-ID IS NULL THEN NULL ELSE C-NAME end,

    D-NAME = CASE WHEN D-ID IS NULL THEN NULL ELSE D-NAME end

  • February 8, 2012 at 12:42 pm

    #1444729

    Something like this?

    Update mytable

    Set [A-Name]=CASE WHEN [A-ID] IS Null THEN null ELSE [A-Name] END,

    [B-Name]=CASE WHEN [B-ID] IS Null THEN null ELSE [B-Name] END

    ...

    As a side note: Ihope the column names are fake...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 8, 2012 at 12:49 pm

    #1444731

    Thanks, all,

    will give a try. Yes, the table and columns name are fake. I just want to make it easier to demonstrate.

    Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

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