question

gcassar avatar image
gcassar asked

Update field in table with Stuffed values from another

A simplified example of my challenge: I have a table called PARKS Fields: ParkID (int) and Sports_Played varchar(200) The parkID values I have match those in my other table SPORT below. I want to populate the Sports_Played field with all the sports from the SPORTS table (concatenated) for that ParkID. ParkID's in the PARKS table: 77666 88544 88966 and here is where I want to get the values from: Table SPORT Fields: ParkID (int) and Sporttype varchar(20) 77666 Tennis 77666 Soccer 77666 Football 88544 Hockey 88544 Soccer 88966 Netball My real situation requires an additional where clause to filter only certain records from the SPORT table but I'm keeping the example simple for now. The result I am looking for is PARKS to look like this: ParkID..........Sports_Played 77666...........Tennis, Soccer, Football etc. I have been playing with the STUFF function and using a 'with' Query feeding into an 'update' command but can't get it to work. Hopefully this is easy for you SQL gurus ! Thanks
concatenation
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The first answer solved part of my problem. I actually had another field called SportPlayed in the Parks table. I wanted to UPDATE the Parks.SportPlayed column with the concatenated Sporttype values where the ParkID's match. I did it with this: Update Parks set SportPlayed = t.SportPlayed from ( SELECT p.ParkID, STUFF(( SELECT ',' + s.Sporttype AS [text()] FROM Sport s WHERE s.ParkID = p.ParkID FOR XML PATH('') ), 1, 1, '' ) AS [SportPlayed] FROM Parks p ) t where Parks.ParkID = t.ParkID
0 Likes 0 ·
CREATE TABLE Parks (ParkID int, SportPlayed varchar(200)) INSERT INTO Parks SELECT 77666, '' UNION SELECT 88544, '' UNION SELECT 88966, '' CREATE TABLE Sport ( ParkID int, Sporttype varchar(20) ) INSERT INTO Sport SELECT 77666, 'Tennis' UNION SELECT 77666, 'Soccer' UNION SELECT 77666, 'Football' UNION SELECT 88544, 'Hockey' UNION SELECT 88544, 'Soccer' UNION SELECT 88966, 'Netball' UPDATE p SET p.SportPlayed = t.SportPlayed FROM Parks p INNER JOIN ( SELECT p.ParkID, STUFF(( SELECT ',' + s.Sporttype AS [text()] FROM Sport s WHERE s.ParkID = p.ParkID FOR XML PATH('') ), 1, 1, '' ) AS [SportPlayed] FROM Parks p ) t ON p.ParkID = t.ParkID
0 Likes 0 ·

1 Answer

·
Sule avatar image
Sule answered
CREATE TABLE Parks (ParkID int) INSERT INTO Parks SELECT 77666 UNION SELECT 88544 UNION SELECT 88966 CREATE TABLE Sport ( ParkID int, Sporttype varchar(20) ) INSERT INTO Sport SELECT 77666, 'Tennis' UNION SELECT 77666, 'Soccer' UNION SELECT 77666, 'Football' UNION SELECT 88544, 'Hockey' UNION SELECT 88544, 'Soccer' UNION SELECT 88966, 'Netball' SELECT p.ParkID, STUFF(( SELECT ',' + s.Sporttype AS [text()] FROM Sport s WHERE s.ParkID = p.ParkID FOR XML PATH('') ), 1, 1, '' ) AS [SportPlayed] FROM Parks p
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.