Click here to Skip to main content
15,299,443 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on SQL server 2014 I need to rewrite update statement with best practice
CAN I Write it without subquery ?

What I have tried:

what i try
SQL
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
Posted
Updated 4-Jan-22 22:12pm
v2
Comments
0x01AA 4-Jan-22 15:47pm
   
Where is a subquery here?
ahmed_sa 4-Jan-22 15:50pm
   
thanks for reply
are statement above write best practice correct
ahmed_sa 4-Jan-22 15:51pm
   
subquery as below
SET FamilyGroup = STUFF(
(
)

1 solution

You do seem to be obsessed with character-separated data in columns - which is bad practice by the way.

Based on the other questions you have been posting this is about performance when using FOR XML PATH because it requires a correlated sub-query i.e. this is the bit that is causing the issue
SQL
ff.Masked_ID=FFFFF.Masked_ID
As you have been advised previously - try putting that data into a temporary table so that you are not impacting the larger query e.g.
SQL
declare @MultiMaskDemo table (FamilyGroupText varchar(10), Masked_ID int)
insert into @MultiMaskDemo ( FamilyGroupText,Masked_ID) values
('A',1), ('B',1),('C',1),('D',2),('E',2),('F',3);

select F2.Masked_ID, FamilyGroup = STUFF(
(		
	SELECT  '|' + CAST( ISNULL(REPLACE(FamilyGroupText,'|','/'),'NULL') AS NVARCHAR(50))
	FROM @MultiMaskDEMO f1
	WHERE f1.Masked_ID = F2.Masked_ID
    FOR XML PATH ('')
),1,1,'')
INTO #temp
FROM  @MultiMaskDemo F2
GROUP BY Masked_ID
You can then join that temporary table to your main query based on Masked_ID. When you put your joins back into this demo code you can change
SQL
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID AND ptt.Masked_ID = FFFFF.Masked_ID
to be
SQL
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID
- No need for two references to the outer query.

As an aside, I strongly advise you to stop using character-separated values in columns - create a many-to-one relationship table for storage and only generate the CSV when required (if at all)
   
Comments
Maciej Los 6-Jan-22 14:37pm
   
You're very kind to this member...
5ed!
CHill60 6-Jan-22 14:47pm
   
I'm not sure how long for...
Maciej Los 6-Jan-22 14:48pm
   
:D

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900