Using CTE and Update in Redshift

I am converting some SQL Logic from T-SQL used in SSMS to Amazon Redshift. I believe Redshift is a fork of Postgres version 8.0.2 so the below may not be possible unless using Postgres 9.1.

WITH CTE_ID AS 
(
SELECT FULL_NAME, COUNT(DISTINCT ID) as ID_COUNT, MAX(ID) AS MAX_ID
FROM MEMBERS
GROUP BY FULL_NAME
HAVING COUNT(DISTINCT ID) > 1
)
UPDATE a
SET a.ID = b.MAX_ID
FROM MEMBERS a
INNER JOIN CTE_ID b
ON a.FULL_NAME = b.FULL_NAME

If this feature is not supported by Amazon Redshift, would my best option be to create a new “temporary” table and populate it with the values the CTE would generate?

Answer

You can re-write the query as a derived table as mentioned by @a_horse_with_no_name:

UPDATE MEMBERS
SET a.ID = b.MAX_ID
FROM MEMBERS a
INNER JOIN (
  SELECT FULL_NAME, COUNT(DISTINCT ID) as ID_COUNT, MAX(ID) AS MAX_ID
  FROM MEMBERS
  GROUP BY FULL_NAME
  HAVING COUNT(DISTINCT ID) > 1
  ) b
ON a.FULL_NAME = b.FULL_NAME

Advertisement

Answer