SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update with inner join problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yoyosh
Starting Member

27 Posts

Posted - 02/27/2013 :  07:19:05  Show Profile  Reply with Quote
I would like to convert 1:N relation into comma-separated value in one column. Suppose we have the following tables:

A:
pk|col2
_______
1 | (empty string)
2 | (empty string)

B:
pk|fk|col3
__________
1 |1 | a
2 |1 | b
3 |2 | c

I want to update col2 in table A in the following way:
A:
pk|col2
_______
1 | a,b
2 | c

I tried something like that:
UPDATE A SET col2 += col3
from A
INNER JOIN B on A.pk = B.fk

Thank you for help in advance

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/27/2013 :  07:51:36  Show Profile  Reply with Quote
UPDATE A SET
	col2 = STUFF(b.col3concat,1,1,'')
FROM
	A
	CROSS APPLY
	(
		SELECT ',' + b.col3
		FROM B 
		WHERE b.fk = a.pk
		FOR XML PATH('')
	) c(col3concat);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/27/2013 :  22:59:43  Show Profile  Reply with Quote
quote:
Originally posted by James K

UPDATE A SET
	col2 = STUFF(bc.col3concat,1,1,'')
FROM
	A
	CROSS APPLY
	(
		SELECT ',' + b.col3
		FROM B 
		WHERE b.fk = a.pk
		FOR XML PATH('')
	) c(col3concat);




Fixed typo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 02/28/2013 :  03:19:16  Show Profile  Reply with Quote
quote:
Originally posted by James K

UPDATE A SET
	col2 = STUFF(b.col3concat,1,1,'')
FROM
	A
	CROSS APPLY
	(
		SELECT ',' + b.col3
		FROM B 
		WHERE b.fk = a.pk
		FOR XML PATH('')
	) c(col3concat);




Thank you for response.

Could you please explain:
c(col3concat) ?
What is 'c' here?

Also second question: is XML PATH necessary in this query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/28/2013 :  04:01:41  Show Profile  Reply with Quote
quote:
Originally posted by yoyosh

quote:
Originally posted by James K

UPDATE A SET
	col2 = STUFF(b.col3concat,1,1,'')
FROM
	A
	CROSS APPLY
	(
		SELECT ',' + b.col3
		FROM B 
		WHERE b.fk = a.pk
		FOR XML PATH('')
	) c(col3concat);




Thank you for response.

Could you please explain:
c(col3concat) ?
What is 'c' here?

Also second question: is XML PATH necessary in this query?


c is short name for the table called table alias
FOR XML PATH is that which causes the concatenation of the string

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 02/28/2013 :  05:30:53  Show Profile  Reply with Quote
Why is alias followed by (...)?

c(col3concat)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/28/2013 :  05:40:14  Show Profile  Reply with Quote
It is telling SQL Server that what comes out from the subquery is a virtual table, and that I want to name that virtual table as "c", and that there will be one column in that table, and that I want to name that column as col3concat. I could name it anything I want - for example:
UPDATE A SET
	col2 = STUFF(foo.bar,1,1,'')
FROM
	A
	CROSS APPLY
	(
		SELECT ',' + b.col3
		FROM B 
		WHERE b.fk = a.pk
		FOR XML PATH('')
	) AS foo(bar);
Why foo and bar? I have no idea! People seem to like foo and bar for things for which they cannot come up with reasonable names. I am sure there is a very good reason for it, but I don't know what that is.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/28/2013 :  12:27:28  Show Profile  Reply with Quote
Probablt mroe info then you ever wanted to know about Foo Bar:
http://www.ietf.org/rfc/rfc3092.txt
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/28/2013 :  12:43:31  Show Profile  Reply with Quote
A man-page for foo bar! Another one from Wiki with pictures and all: http://en.wikipedia.org/wiki/Foobar
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 03/04/2013 :  03:50:37  Show Profile  Reply with Quote
Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000