在Redshift中使用CTE和更新 [英] Using CTE and Update in Redshift

问题描述

我正在将一些SQL逻辑从SSMS中使用的T-SQL转换为Amazon Redshift.我相信Redshift是Postgres 8.0版的分支.2 ，因此除非使用Postgres 9.1，否则以下可能无法实现.

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

如果Amazon Redshift不支持此功能，我的最佳选择是创建一个新的临时"表并用CTE生成的值填充它吗?

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?

推荐答案

您可以将查询重新编写为派生表，如@a_horse_with_no_name所述:

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

UPDATE a
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

这篇关于在Redshift中使用CTE和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！