locked
Insert Serial RRS feed

  • Question

  • Hi

    I have a table as

    Code                     Serial

    100801                  4001

    100801                  4002

    100801                 

    100802                  89

    100802 

    100810                  700

    100810

    100810

     

    I want to add serial max + 1 where it is null group by Code so the result should be like using SQL Server 2008 R2

    Code                     Serial

    100801                  4001

    100801                  4002

    100801                  4003

    100802                  89

    100802                  90

    100810                  700

    100810                  701

    100810                  702


    Regards, Muhammad Bilal.

    Friday, July 22, 2016 4:40 PM

Answers

  • Declare @Sample Table(Code int, Serial int);
    Insert @Sample(Code,Serial) Values
    (100801,                  4001),
    (100801,                  4002),
    (100801,                 NULL),
    (100802,                  89),
    (100802,                 NULL),
    (100810,                  700),
    (100810,                NULL),
    (100810,                NULL);
    
    ;With cteMax As
    (Select Code, Max(Serial) As MaxSerial
    From @Sample
    Group By Code),
    cteRN As
    (Select Code, Serial, Row_Number() Over(Partition By Code Order By Code) As rn
    From @Sample
    Where Serial Is Null)
    Update r
    Set Serial = IsNull(c.MaxSerial, 0) + rn
    From cteRN r
    Left Join cteMax c On c.Code = r.Code;
    
    Select * From @Sample;

    Tom
    • Proposed as answer by Rafael Juca Friday, July 22, 2016 5:03 PM
    • Marked as answer by Muhammad Bilal Friday, July 22, 2016 5:38 PM
    Friday, July 22, 2016 4:59 PM

All replies

  • Declare @Sample Table(Code int, Serial int);
    Insert @Sample(Code,Serial) Values
    (100801,                  4001),
    (100801,                  4002),
    (100801,                 NULL),
    (100802,                  89),
    (100802,                 NULL),
    (100810,                  700),
    (100810,                NULL),
    (100810,                NULL);
    
    ;With cteMax As
    (Select Code, Max(Serial) As MaxSerial
    From @Sample
    Group By Code),
    cteRN As
    (Select Code, Serial, Row_Number() Over(Partition By Code Order By Code) As rn
    From @Sample
    Where Serial Is Null)
    Update r
    Set Serial = IsNull(c.MaxSerial, 0) + rn
    From cteRN r
    Left Join cteMax c On c.Code = r.Code;
    
    Select * From @Sample;

    Tom
    • Proposed as answer by Rafael Juca Friday, July 22, 2016 5:03 PM
    • Marked as answer by Muhammad Bilal Friday, July 22, 2016 5:38 PM
    Friday, July 22, 2016 4:59 PM
  • >> I have a table as <<

    No you do not have a table. Where is the DDL? What are the keys? You do know the table has to have a key, by definition, don't you? There is no such thing and a valid data model as a generic "code" or generic "serial"; we do not even know the name of this table. Have you ever had a course in basic data modeling or RDBMS?

    In the picture that you drew, there is only one possible key. What you are doing is a very typical newbie disaster. You create table without keys, loaded with invalid data, and try to correct it after the fact. This is the worst possible way to use SQL.

    CREATE TABLE Foobar
    (foobar_group CHAR(6) NOT NULL
      CHECK (foobar_group LIKE'[0-9][0-9][0-9][0-9][0-9][0-9]'),
     something_serial_nbr INTEGER NOT NULL,
     PRIMARY KEY (foobar_group, something_serial_nbr)
    );

    INSERT INTO Foobar
    VALUES
    ('100801', 4001),
    ('100801', 4002),
    ('100801', 4003),
    ('100802', 89),
    ('100802', 90),
    ('100810', 700),
    ('100810', 701),
    ('100810', 703);

    >> I want to add serial max + 1 where it is NULL group by <<

    No! Look at your group 100810; it has two null columns in your original non-table. Since SQL is a set oriented language both of the something_serial_nbr would have been set to 701. You are depending on some kind of sequential order. But tables have no ordering, by definition. Tom is giving you a nice kludge that uses a row number, and proprietary undependable update.. from.. statement. But your data is still in a mess. 

    If your serial numbers have no gaps, why are you enumerating them?

    CREATE TABLE Foobar2
    (foobar_group CHAR(6) NOT NULL
      CHECK (foobar_group LIKE'[0-9][0-9][0-9][0-9][0-9][0-9]'),
     low_serial_nbr INTEGER NOT NULL,
     high_serial_nbr INTEGER NOT NULL,
     CHECK (low_serial_nbr <=  high_serial_nbr)
     PRIMARY KEY (foobar_group, something_serial_nbr)
    );

    INSERT INTO Foobar2
    VALUES
    ('100801', 4001, 4003),
    ('100802', 89, 90),
    ('100810', 700, 703);


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, July 22, 2016 6:33 PM