UPDATE "public"."productpackage" pkg
set package = tmp.package
-- purity = substring(package from '\(([^)]+)\)')
FROM (
with t as (

SELECT
id,
cat_no,
package old_package,
regexp_replace(trim(substring(package from '(.+)(\([^)]+\))?')),'(?<=\d) (?=[a-zμ])','', 'g') package,
substring(package from '\(([^)]+)\)') purity,
row_number() over(partition by cat_no, regexp_replace(trim(substring(package from '(.+)(\([^)]+\))?')),'(?<=\d) (?=[a-zμ])','') ORDER BY id desc) r
FROM "public"."productpackage"

WHERE "brand" = 'mce'
AND "package" LIKE '%(%'
)

SELECT
*
FROM t
where r=1


) tmp
where pkg.id = tmp.id