0

Good morning, I hope this is an easy one for you experts. I have a temporary table that has a field called 'mailer'.

The 'mailer' field can hold mutiple values concantenated from a dual join.

I am including the design, as well as the joins.

-- code to create the tables and data

create table table_a
(
    ContactID varchar(100),
    Mailer varchar(max)
)

create table table_b
(
    ContactID varchar(100),
    MailerID varchar(100)
)
create table table_c
(
    MailerID varchar(100),
    MailerName varchar(100)
)

insert into table_a
VALUES ('1',''),
    ('2',''),
    ('3',''),
    ('4',''),
    ('5','');

insert into table_B
VALUES ('1','MAIL1'),
    ('1','MAIL2'),
    ('1','MAIL3'),
    ('2','MAIL2'),
    ('2','MAIL4'),
    ('2','MAIL5'),
    ('3','MAIL3'),
    ('4','MAIL4'),
    ('5','MAIL5');

insert into table_c
VALUES ('MAIL1','Mailer1'),
    ('MAIL2','Mailer2'),
    ('MAIL3','Mailer2'),
    ('MAIL4','Mailer4'),
    ('MAIL5','Mailer5');

Relationships would be like this:

  • Table_A.Contactid may be in Table_B (ContactID)
  • Table_B.MailerID would alwasy be in Table_C (Mailerid)

What I need is an update that will run thru table_A and update the actual Mailer name from Table_C if the contactID in Table_B matches the contactID in table_A

After the update I need table_A to look like this: Table_A.ContactID, Table_A.Mailer
('1','Mailer1;Mailer2;Mailer3')
('2','Mailer2,Mailer4,Mailer5')
('3','Mailer3')
('4','Mailer4')
('5','Mailer5')

SELECT * FROM table_a 

drop table table_a 
drop table table_b
drop table table_c
Paul Richter
  • 10,691
  • 8
  • 49
  • 81
Bryan Holmstrom
  • 1
  • 1
  • Bryan, it seems some of your question text was stuck in the code block. I did the best I could to format the last part for you but you might want to take a glance to ensure it is what you intended. – Paul Richter Jan 14 '14 at 19:34
  • Teeg thanks. The code looks good. I am looking for the update staement that is going to put mailer names from table_c into table_a. – Bryan Holmstrom Jan 14 '14 at 19:39
  • Which db engine are you using (eg: mysql, postgres, sql-server, etc)? – Paul Richter Jan 14 '14 at 19:51
  • [This answer](http://stackoverflow.com/q/15154644/877472) might help. They're grouping and concatenating a column together. You just have to put that in to an update statement. – Paul Richter Jan 15 '14 at 01:46

0 Answers0