Post reply

How to update a null column at very first row creation

  • October 24, 2008 at 11:35 pm

    #96759

    Hello there,

    How to update a null valued column at very first stage?

    Following query did not update (0 rows affected) any data on table why?

    UPdate tabUserInfo

    Set UserID =

    ( Select tUserID =

    Case ISNULL(MAX(UserId),0)

    When 0 Then '1'

    Else (Max(UserID)+1)

    End

    From tabUserInfo

    )

    Where (UserID Is Null or UserID <= 0)

    Thanks,

    adyarmail.

  • October 25, 2008 at 9:48 am

    #889649

    First, what do you get when you run the following?

    SELECT * FROM tabUserInfo

    Where (UserID Is Null or UserID <= 0)

    Now, what do you get when you run the following?

    SELECT * FROM tabUserInfo

    I would bet both return zero rows because the table is actually empty.

    This looks like you're trying to build your own method for an IDENTITY column and it's one of the worst ways to do it. My recommendation would be to learn how to use the auto-numbering capabilites of and IDENTITY column or, if you're trying to avoid that because of some replication requirements, learn how to correctly build and process and "sequence" table although a "sequence" table makes for some pretty big problems for code developement in itself.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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