Repeating update or replace statement for column

  • I want to create an update or replace statement which replaces Event_Code column with repeating values instead for example

    B100

    B102

    B103

    ....

    ...

    all the way to 10 and repeat again to the end of the table see results wanted SQL scrip example.

    Is there a way to do this?

    Thanks for the help.

    Create table table1

    (

    Participant_ID int not null

    ,Supporter_ID int not null

    ,Event_Code varchar (50)

    ,Event_Role varchar (100) null

    )

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LELEG_SWI_1995','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','LEG_OXF_1998','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','IsCoordinator');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','No Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','IsCoordinator');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','No Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','IsCoordinator');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','No Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','LEG_OXF_1998','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','LEG_OXF_1998','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','LEG_BUR_0000','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','SEMF091212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','RFSEAH091212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','SEAJ251112','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','SERB031212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','SECW021212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','LM1301','Do Not Register');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','SEGF050912','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','LM1301','Do Not Register');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','SEDB220712','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','GNR0001','Do Not Register');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','ELON09','IsVolunteer');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','ELON09','IsVolunteer');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','ELON09','IsVolunteer');

    ========================================================================

    Wanted results table sample --

    Create table resultswanted

    (

    Participant_ID int not null

    ,Supporter_ID int not null

    ,Event_Code varchar (50)

    ,Event_Role varchar (100) null

    )

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B100','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B102','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B103','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B104','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B105','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B106','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B107','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B108','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B109','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B110','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','B100','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B100','IsCoordinator');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B102','No Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B103','IsCoordinator');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B104','No Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B105','IsCoordinator');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B106','No Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','B107','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','B108','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','B109','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','B110','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','B100','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','B100','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','B102','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','B103','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','B104','Do Not Register');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','B105','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','B106','Do Not Register');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','B107','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','B108','Do Not Register');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','B109','IsVolunteer');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','B110','IsVolunteer');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','B100','IsVolunteer');

    Thanks again

  • use a cartesian product. (two tables in the query, no join). One has People, one has Events. No join means the tables get multiplied. Filter as necessary.

  • azdeji (9/22/2013)


    I want to create an update or replace statement which replaces Event_Code column with repeating values instead for example

    B100

    B102

    B103

    ....

    ...

    all the way to 10 and repeat again to the end of the table see results wanted SQL scrip example.

    Is there a way to do this?

    Thanks for the help.

    Create table table1

    (

    Participant_ID int not null

    ,Supporter_ID int not null

    ,Event_Code varchar (50)

    ,Event_Role varchar (100) null

    )

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LELEG_SWI_1995','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Did Not Attend');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Yes Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','LEG_OXF_1998','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','IsCoordinator');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','No Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','IsCoordinator');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','No Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','IsCoordinator');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','No Reg');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','LEG_OXF_1998','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','LEG_OXF_1998','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','LEG_BUR_0000','Yes Attended');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','SEMF091212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','RFSEAH091212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','SEAJ251112','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','SERB031212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','SECW021212','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','LM1301','Do Not Register');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','SEGF050912','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','LM1301','Do Not Register');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','SEDB220712','Speaker');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','GNR0001','Do Not Register');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','ELON09','IsVolunteer');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','ELON09','IsVolunteer');

    INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','ELON09','IsVolunteer');

    ========================================================================

    Wanted results table sample --

    Create table resultswanted

    (

    Participant_ID int not null

    ,Supporter_ID int not null

    ,Event_Code varchar (50)

    ,Event_Role varchar (100) null

    )

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B100','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B102','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B103','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B104','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B105','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B106','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B107','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B108','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B109','Did Not Attend');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B110','Yes Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','B100','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B100','IsCoordinator');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B102','No Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B103','IsCoordinator');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B104','No Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B105','IsCoordinator');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B106','No Reg');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','B107','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','B108','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','B109','Yes Attended');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','B110','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','B100','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','B100','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','B102','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','B103','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','B104','Do Not Register');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','B105','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','B106','Do Not Register');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','B107','Speaker');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','B108','Do Not Register');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','B109','IsVolunteer');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','B110','IsVolunteer');

    INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','B100','IsVolunteer');

    Thanks again

    I can't for the life of me figure out what sort order you want nor when you want to reset back to B100 from the data you've given. The following code will get you started, though. I hope you know how MODULUS (%) works.

    SELECT Participant_ID

    ,Supporter_ID

    ,Event_Code = 'B'+CAST((ROW_NUMBER() OVER (ORDER BY Participant_ID,Supporter_ID)-1)%11+100 AS VARCHAR(10))

    ,Event_Role

    FROM dbo.Table1

    ORDER BY Participant_ID,Supporter_ID,Event_Code

    ;

    --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)

  • since the order does not matter your code is enough to get me started.

    Thank you.

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

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