Update a field in table from join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update a field in table from join

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-01-14 : 09:48:06

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.

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:
--('1','Mailer1;Mailer2;Mailer3')
--('2','Mailer2,Mailer4,Mailer5')
--('3','Mailer3')
--('4','Mailer4')
--('5','Mailer5')

SELECT * FROM table_a
SELECT * FROM table_b
SELECT * FROM table_c

drop table table_a
drop table table_b
drop table table_c

Bryan Holmstrom

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-14 : 19:09:04

How dose that look?
I hope this will help.

----------------------------------------------


WITH base(ContractID, MailerName) AS (
SELECT
	table_a.ContactID,
	table_c.MailerName
FROM table_a
LEFT JOIN table_b
	ON table_a.ContactID = table_b.ContactID
LEFT JOIN table_c
	ON table_b.MailerID = table_c.MailerID
),
forupdate(ContractID, Mailer) AS (
SELECT DISTINCT
	base.ContractID,
	(
		SELECT
			subbase.MailerName + ',' --If you use ';' for deliminator, change here.
		FROM base subbase
		WHERE base.ContractID = subbase.ContractID
		FOR XML PATH('')
	) Mailer
FROM base
)
UPDATE table_a
	SET Mailer = 
		CASE
			WHEN 0 < LEN(forupdate.Mailer) THEN LEFT(forupdate.Mailer, LEN(forupdate.Mailer) - 1)
			ELSE ''
		END --Formatting by case, remove deliminator at the end .
FROM forupdate
WHERE table_a.ContactID = forupdate.ContractID

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:25:19

[code]
UPDATE a
SET Mailer = STUFF((SELECT ';' + c.MailerName
FROM table_c c
INNER JOIN table_b b
ON b.MailerID = c.MailerID
WHERE b.ContactID = a.ContactID
ORDER BY c.MailerID
FOR XML PATH('')),1,1,'')
FROM table_a a

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources