Repeating update or replace statement for column

• azdeji

  SSCrazy

  Points: 2535

  More actions

  September 22, 2013 at 9:21 am

  #280016

  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

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  September 22, 2013 at 10:40 am

  #1652196

  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.

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  September 22, 2013 at 11:25 am

  #1652197

  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)
  

• azdeji

  SSCrazy

  Points: 2535

  More actions

  September 22, 2013 at 1:36 pm

  #1652206

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

  Thank you.