January 25, 2011 at 2:32 am
Hi,
I am trying to update a table from another, but concatenate all the data from the secondary table into the first.
i.e. If ID 1 had 3 surname records (Smith, Jones, Brown) in TableB, then in TableA I'd want "SmithJonesBrown".
At the moment I can do it in a while loop, but was hoping there was something quicker/cleaner:
DECLARE@vDataVARCHAR(500),
@iCounterINT
SET@iCounter = 1
WHILE @iCounter < (SELECT MAX(ID) FROM dbo.TableA)
BEGIN
SET@vData = ''
SELECT@vData = @vData + b.ColumnB
FROMdbo.TableB
WHEREID = @iCounter
UPDATEdbo.TableA
SETColumnA = @vData
WHEREID = @iCounter
SET@iCounter = @iCounter + 1
END
Something like:
UPDATEa
SETColumnA = a.ColumnA + b.ColumnB
FROMdbo.TableA a
INNER JOIN
dbo.TableB b
ONa.ID = b.ID
but that actually works (this only gets the last record)!
If it's not possible, then please let me know as I'll stick with what I have...
Thanks,
Tom
January 25, 2011 at 2:41 am
you can use:
Update TableA set colA=ColA + TableB.ColB
from TableB where tableA.id = tableB.id
January 25, 2011 at 2:45 am
How about this ?
DECLARE @First_Table TABLE
(
ID INT,
Full_name VARCHAR(250)
)
INSERT INTO @First_Table (ID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
DECLARE @Second_Table TABLE
(
ID INT,
Split_name VARCHAR(50)
)
INSERT INTO @Second_Table (ID, Split_name)
SELECT 1 , 'Catherine' UNION ALL
SELECT 1 , 'Zeta' UNION ALL
SELECT 1 , 'Jones' UNION ALL
SELECT 2 , 'Sachin' UNION ALL
SELECT 2 , 'Ramesh' UNION ALL
SELECT 2 , 'Tendulkar' UNION ALL
SELECT 3 , 'Roger' UNION ALL
SELECT 3 , 'Tennis' UNION ALL
SELECT 3 ,'Federer'
SELECT * FROM @First_Table
UPDATE FT
SET FT.Full_name =
REPLACE ( STUFF ( ( SELECT ','+Split_name
FROM @Second_Table ST
WHERE FT.ID = ST.ID
ORDER BY ID
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) , ',','')
FROM @First_Table FT
SELECT * FROM @First_Table
January 25, 2011 at 2:55 am
I had my mind at sea while using that. A much tweaked version of the UPDATE statement (uses the same logic but with some tweak)
UPDATE FT
SET FT.Full_name =
( SELECT Split_name+''
FROM @Second_Table ST
WHERE FT.ID = ST.ID
ORDER BY ID
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
FROM @First_Table FT
January 25, 2011 at 3:09 am
Thanks ColdCoffee - that's perfect! 🙂
Can use this in a couple of other places so your help is very much appreciated
January 25, 2011 at 3:55 am
:blush: thanks for the feedback Tom!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply