Professional Web Applications Themes

Autoincrementing a column for rows with null - Microsoft SQL / MS SQL Server

Is there a way, in a Select statement, to 'auto-increment' a column with numbers to only affect the rows with nulls in them? Obviously, this will not work... select col1 = identity(int,1,1) ... because some rows have values. I do not need to worry about what the value is, they just need to 'count off'. Jason...

  1. #1

    Default Autoincrementing a column for rows with null

    Is there a way, in a Select statement, to 'auto-increment' a column with
    numbers to only affect the rows with nulls in them?

    Obviously, this will not work...

    select col1 = identity(int,1,1)

    ... because some rows have values. I do not need to worry about what the
    value is, they just need to 'count off'.

    Jason


    Jason Guest

  2. #2

    Default Re: Autoincrementing a column for rows with null

    I kinda knew this already. I was hoping for a way to do it during the select
    statement, rather than using an update, but that is OK. This will work!

    Thanks!

    Jason

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:uAEEy3NQDHA.2852tk2msftngp13.phx.gbl...
    > UPDATE YourTable SET Col1 = (SELECT COUNT(*) + 1 FROM YourTable AS b
    > WHERE b.Col1 IS
    NOT
    > NULL)
    > WHERE Col1 IS NOT NULL
    >
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Jason" <Jason.Lipmanstate.sd.us> wrote in message
    > news:OgmYJiNQDHA.2700tk2msftngp13.phx.gbl...
    > > Is there a way, in a Select statement, to 'auto-increment' a column with
    > > numbers to only affect the rows with nulls in them?
    > >
    > > Obviously, this will not work...
    > >
    > > select col1 = identity(int,1,1)
    > >
    > > .. because some rows have values. I do not need to worry about what the
    > > value is, they just need to 'count off'.
    > >
    > > Jason
    > >
    > >
    >
    >

    Jason Guest

  3. #3

    Default Re: Autoincrementing a column for rows with null

    If your inserting this into a table then just use INSERT instead of update.
    Either way should work.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Jason" <Jason.Lipmanstate.sd.us> wrote in message
    news:OyFQpOOQDHA.1684TK2MSFTNGP12.phx.gbl...
    > I kinda knew this already. I was hoping for a way to do it during the
    select
    > statement, rather than using an update, but that is OK. This will work!
    >
    > Thanks!
    >
    > Jason
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > news:uAEEy3NQDHA.2852tk2msftngp13.phx.gbl...
    > > UPDATE YourTable SET Col1 = (SELECT COUNT(*) + 1 FROM YourTable AS b
    > > WHERE b.Col1 IS
    > NOT
    > > NULL)
    > > WHERE Col1 IS NOT NULL
    > >
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "Jason" <Jason.Lipmanstate.sd.us> wrote in message
    > > news:OgmYJiNQDHA.2700tk2msftngp13.phx.gbl...
    > > > Is there a way, in a Select statement, to 'auto-increment' a column
    with
    > > > numbers to only affect the rows with nulls in them?
    > > >
    > > > Obviously, this will not work...
    > > >
    > > > select col1 = identity(int,1,1)
    > > >
    > > > .. because some rows have values. I do not need to worry about what
    the
    > > > value is, they just need to 'count off'.
    > > >
    > > > Jason
    > > >
    > > >
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

Similar Threads

  1. Adding more rows to one column/cell only?
    By kate_charlwood@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: October 18th, 12:47 PM
  2. find null value in any column
    By wilson.sh.tam@gmail.com in forum MySQL
    Replies: 7
    Last Post: July 18th, 05:44 PM
  3. Two column header rows possible?
    By Bill Musgrave in forum ASP.NET Data Grid Control
    Replies: 6
    Last Post: December 23rd, 04:12 PM
  4. Looping through all rows and columns (to get sub-totals for each column and row)
    By Ralph Hartman in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: August 25th, 12:00 PM
  5. How to ensure if column A is null, column B has to be null
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 07:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139