How to rewrite update statment with best practise and performance ?

ahmed elbarbary

4y889

I work on SQL server 2014 I need to rewrite update statement with best practice

CAN I Write it without subquery ?

  UPDATE  FFFFF
SET  FamilyGroup = STUFF(
(		
SELECT   DISTINCT '|' + CAST( ISNULL(REPLACE(f.FamilyGroupText,'|','/'),'NULL') AS NVARCHAR(50))
FROM ExtractReports.dbo.MultiMask FF 
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID AND ptt.Masked_ID = FFFFF.Masked_ID
left JOIN [Parts].partscrubbing LC WITH(NOLOCK) ON ptt.PartID=LC.PartID
LEFT JOIN  Parts.ZFamilyGroup f ON f.ID=LC.Generic_3
WHERE ff.Masked_ID=FFFFF.Masked_ID
        FOR XML PATH ('')
	
),1,1,'')
FROM  ExtractReports.dbo.MultiMask FFFFF

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Unfortunately, I am not getting what you are doing with STUFF function in your query.

For a moment, keep the update part of the query aside.

Can you please give a quey which would return just following two columns:

1/ Masked_Id - primary key required for the update later and...
2/ xmlData column which you are fetching in sub-query.

If you manage to do that, rest of the part is very easy.

You can see the example, at the following link:

https://www.sqlshack.com/how-to-update-from-a-select-statement-in-sql-server/

Hope it helps!
ahmed elbarbary4y ago
can you make it please
or give me sample
if you can't do on my update
give me sample for any data you have
Nitin Sontakke4y ago
I may not be able to give you a working solution, but let me try to give you a hint.

Create a table variable and insert the Id column of table you want to update and the value you want to update.

Then join the table you want to update with above table.

This way much of hard work is done separately. And update is fired separately.

Much easier if a stored procedure solution is acceptable. However, it can work without one as well.