Update table from nested virtual table query

Originally published on 2017-09-25

Simply running update of table from nested query on the same table won’t work

UPDATE xx  
SET YY  
WHERE ZZ = SELECT _something_ FROM XX

Need to define virtual sub-table in same query using _with_ keyword

WITH b AS
  (
    SELECT productgroup.name, customer.rparent, customer.id
    FROM productgroup
    INNER JOIN customer
    ON UPPER(productgroup.name) LIKE UPPER(CONCAT(customer.rparent, '%'))
  )
UPDATE productgroup
SET custid = b.id
FROM b
WHERE b.name = productgroup.name;