I work on sql server 2017 i need to ask
are separate update will be best or do all update on one step ?
so using only one update to update all columns
or
using only one update for every column update ?
What I have tried:
UPDATE TT
SET TT.strSubstances = CAST (STUFF(( SELECT ','+ CAST(CC.Substance AS VARCHAR(3500))
FROM Parts.ChemicalProfiles CC with(nolock)
WHERE CC.ChemicalID = TT.ChemicalID
ORDER BY CC.Substance
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500)) ,
TT.strMass = CAST (STUFF(( SELECT ','
+ convert(VARCHAR(3500),CC.Mass,128)--CAST(CC.Mass AS VARCHAR(3500))
FROM Parts.ChemicalProfiles CC with(nolock)
WHERE CC.ChemicalID = TT.ChemicalID
ORDER BY CC.Mass
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500)),
TT.strCASNumber = CAST (STUFF(( SELECT ','
+ CAST(CC.CASNumber AS VARCHAR(3500))
FROM Parts.ChemicalProfiles CC with(nolock)
WHERE CC.ChemicalID = TT.ChemicalID
ORDER BY CC.CASNumber
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500)),
TT.strHomogeneousMaterialName = CAST (STUFF(( SELECT ','
+ CAST(CC.HomogeneousMaterialName AS VARCHAR(3500))
FROM Parts.ChemicalProfiles CC with(nolock)
WHERE CC.ChemicalID = TT.ChemicalID
ORDER BY CC.HomogeneousMaterialName
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500)),
--strHomogeneousMaterialMass
TT.strHomogeneousMaterialMass = CAST (STUFF(( SELECT ','
+ convert(VARCHAR(3500),CC.HomogeneousMaterialMass,128)--CAST(CC.Mass AS VARCHAR(3500))
FROM Parts.ChemicalProfiles CC with(nolock)
WHERE CC.ChemicalID = TT.ChemicalID
ORDER BY CC.HomogeneousMaterialMass
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500))
FROM #ChemeicalIDCounts TT
OR
DO separate update as below
UPDATE TT
SET TT.strSubstances = CAST (STUFF(( SELECT ','+ CAST(CC.Substance AS VARCHAR(3500))
FROM Parts.ChemicalProfiles CC with(nolock)
WHERE CC.ChemicalID = TT.ChemicalID
ORDER BY CC.Substance
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500))
FROM #ChemeicalIDCounts TT
UPDATE TT
SET TT.strMass = CAST (STUFF(( SELECT ','
+ convert(VARCHAR(3500),CC.Mass,128)--CAST(CC.Mass AS VARCHAR(3500))
FROM Parts.ChemicalProfiles CC with(nolock)
WHERE CC.ChemicalID = TT.ChemicalID
ORDER BY CC.Mass
FOR
XML PATH('')
), 1, 1, '') AS NVARCHAR(3500))
FROM #ChemeicalIDCounts TT
etc..