none
Inserting sequence value for one to many? RRS feed

  • Question

  • Hi Experts,

    I've a table in that I've columns like PersonID, DiscountCode, startdate

    now, for one personID I 've many DiscountCOdes like below

    PersonID           DiscountCode

    AAA                   1233

    AAA                     1765

    AAA                    1227

    BTD                    1651

    I've to insert a new column Sequence, for AAA 1233 sequence nbr should be 1, for AAA 1765 -->2, for AAA 1227 --> 3 and for BTD 1651 it should be again 1.

    is there any way to do it with the help of script instead of manual insert each time.

    Thanks in advance

    --Noorbi

    Friday, March 22, 2013 4:44 AM

Answers

  • Try this :

    declare @t table (PersonID  varchar(10),DiscountCode INT,ID INT)
    
    insert into @t(PersonID,DiscountCode)
    values('AAA',1233),('AAA',1765),('AAA',1227),('BTD',1651)
    
    ;with cte as 
    (select *,ROW_NUMBER() over(partition by personID order by personID) as rowID 
    from @t
    )
    
    update cte
    set ID = rowID
    
    
    select * from @t


    Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by SQL2012BI Friday, March 22, 2013 5:18 AM
    Friday, March 22, 2013 4:51 AM

All replies

  • Try this :

    declare @t table (PersonID  varchar(10),DiscountCode INT,ID INT)
    
    insert into @t(PersonID,DiscountCode)
    values('AAA',1233),('AAA',1765),('AAA',1227),('BTD',1651)
    
    ;with cte as 
    (select *,ROW_NUMBER() over(partition by personID order by personID) as rowID 
    from @t
    )
    
    update cte
    set ID = rowID
    
    
    select * from @t


    Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by SQL2012BI Friday, March 22, 2013 5:18 AM
    Friday, March 22, 2013 4:51 AM
  • Hi Paresh,

    Thank you for the quick response, but is there any way to avoid manual insert as there are many records in table.

    because everytime i've to truncate and reload the table and do this sequence inserts.

    --Noorbi

    Friday, March 22, 2013 4:55 AM
  • Thank you Paresh for the hint given, I little tweek the query and bingo I got the result I want with less effort.

    Thanks a lot.

    Friday, March 22, 2013 5:19 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Does your boss make you program from vague descriptions and pictures? When you have a start_date, you have to have an end_date and the pair has to be an attributive of something in particular. 

    You need to read a book on data modeling. You are not doing this right. 

    CREATE TABLE Customer_Discounts
    (customer_id CHAR(10) NOT NULL,
     discount_code CHAR(4) NOT NULL,
     PRIMARY KEY (customer_id, discount_code), 
     discount_start_date DATE NOT NULL,
     discount_end_date DATE);

    INSERT INTO Customer_Discounts
    VALUES ('AAA', '1233', '2013-03-25', NULL),
    ('AAA', '1765', '2013-03-25', NULL),
    ('AAA', '1227', '2013-03-25', NULL),
    ('BTD', '1651', '2013-03-25', NULL),

    >> I have to insert a new column “<something>_seq”, for 'AAA', 1233 sequence number should be 1, for 'AAA', 1765 -->2, for 'AAA', 1227 --> 3 and for BTD 1651 it should be again 1. <<

    Why? What does this new attribute mean in the data model? 

    >> is there any way to do it with the help of script instead of manual insert each time. <<

    What is the business rule for assigning the “<something>_seq”?  Surely you did not mean to use the physical order of the physical records on the physical disk that holds the logical rows of the logical rows! Try this:

    CREATE VIEW Silly_Sequenced_Customer_Discounts
    AS
    SELECT customer_id, discount_code,
           ROW_NUMBER() 
            OVER (PARTITION BY customer_id 
                     ORDER BY discount_start_date, discount_code)
           AS discount_seq
     FROM Customer_Discounts;

    --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, March 22, 2013 2:19 PM
  • Thank you CELKO for the detailed explanation on the basics to follow and stress on following good practices while coding. I respect your words and mind them while doing the code.

    Thank you again.

    Monday, March 25, 2013 10:33 AM