How to update a column by a result from RECURSIVE CTE

SQL
1

I UPDATE my column by a result from recursive cte.

  • My raw data has 20k rows.
  • My recursive cte run smoothly then give me the result with 80k.

Then I use UPDATE statement by a result I got from CTE. My expectation is to see the new data with 80k updated rows. But it only run and update 20k rows.
I wonder Where did I go wrong ? Any comments, ideas would be very appreciated. Thanks a lot!

Here’s my query:

WITH cte_split(Details, split_value, Sizes) AS
(
-- anchoring member
SELECT
	Details,
	CAST(LEFT(Sizes, CHARINDEX(',', Sizes + ',')-1) AS nvarchar),
	STUFF(Sizes, 1, CHARINDEX(',', Sizes + ','), '')
FROM PortfolioProject.dbo.Fashion
UNION ALL

-- recursive member
SELECT
	Details,
	CAST(LEFT(Sizes, CHARINDEX(',', Sizes + ',')-1) AS nvarchar),
	STUFF(Sizes, 1, CHARINDEX(',', Sizes + ','), '')
FROM cte_split
WHERE Sizes > ''
),
cte_02 AS(
SELECT pf.new_sizes, cs.split_value
FROM cte_split cs
	JOIN PortfolioProject.dbo.Fashion pf ON cs.Details = pf.Details
)
UPDATE cte_02
SET new_sizes = split_value