Set language variant for Umbraco Nested Content properties

set-variant.sql

-- Set language ID for variant properties where the CMS has not already updated them
BEGIN TRAN;
DECLARE @lang int;
SELECT TOP 1 @lang = id FROM umbracoLanguage;

UPDATE umbracoPropertyData
SET languageId = @lang
WHERE id IN 
(SELECT id FROM (
  SELECT pd.versionId, pd.propertyTypeId, MAX(pd.languageId) as lang, MAX(pd.id) as id FROM umbracoPropertyData pd
  JOIN [umbracoContentVersion] cv ON pd.[versionId] = cv.id
  WHERE propertyTypeId IN (

SELECT [id]
  FROM [cmsPropertyType]
  WHERE variations > 0
  )
  GROUP BY versionId, propertyTypeId
  ) a
  WHERE a.lang IS NULL);

  COMMIT TRAN;
  
-- Remove non-variant properties from blueprints
DELETE FROM umbracoPropertyData WHERE id IN (
	SELECT pd.id FROM umbracoPropertyData pd
	JOIN [umbracoContentVersion] cv ON pd.[versionId] = cv.id
	JOIN umbracoNode n ON n.Id = cv.nodeId
	WHERE pd.languageId IS NULL AND nodeObjectType = '6EBEF410-03AA-48CF-A792-E1C1CB087ACA'
	AND propertyTypeId IN (

SELECT [id]
	FROM [cmsPropertyType]
	WHERE variations > 0
	)
)