Update with inner join problem

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 2012 Forums

Transact-SQL (2012)

Update with inner join problem

Author

Topic

yoyosh
Starting Member

27 Posts

Posted - 2013-02-27 : 07:19:05

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 07:51:36

[code]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);[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 22:59:43

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/

yoyosh
Starting Member

27 Posts

Posted - 2013-02-28 : 03:19:16

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 04:01:41

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/

yoyosh
Starting Member

27 Posts

Posted - 2013-02-28 : 05:30:53

Why is alias followed by (...)?

c(col3concat)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 05:40:14

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.