Redshift SQL：如何实现同表自连接更新前一行对应字段数据？

Got it, let's work through this Redshift update problem. The self-join approach you tried (UPDATE tab SET tab.fieldA = tabPrior.fieldA FROM tab tabPrior WHERE tab.version = tabPrior.version + 1) won't work here because Redshift—being an MPP columnar database—restricts modifying a table while reading from it in the same statement. This is to maintain data consistency across its distributed nodes.

Here are two reliable methods to achieve your goal:

Method 1: Use the LAG() Window Function with a CTE #

The LAG() window function is perfect for grabbing the previous row's value directly, no self-join needed. We'll first compute all the prior fieldA values in a CTE, then join that back to the original table for the update:

WITH prior_field_values AS (
    SELECT
        version,
        -- Get fieldA from the row with version = current version - 1
        LAG(fieldA) OVER (ORDER BY version) AS prior_fieldA
    FROM tab
)
UPDATE tab
SET fieldA = pfv.prior_fieldA
FROM prior_field_values pfv
WHERE tab.version = pfv.version
-- Exclude the first row (it has no prior row, so LAG returns NULL)
AND pfv.prior_fieldA IS NOT NULL;

Why this works: #

The CTE runs first, calculating all the prior values in a single pass over the table. Then we join this precomputed dataset to the original table for the update—Redshift handles this safely because we're no longer reading and modifying the same table in the same step.

Method 2: Use a Temporary Table (Great for Large Datasets) #

If you're working with a huge table, using a temporary table can be more efficient since Redshift optimizes temp table operations. Here's how:

-- Create a temp table to store each version and its prior fieldA value
CREATE TEMP TABLE tab_prior_data AS
SELECT
    version,
    LAG(fieldA) OVER (ORDER BY version) AS prior_fieldA
FROM tab;

-- Perform the update by joining the temp table to the original
UPDATE tab
SET fieldA = tpd.prior_fieldA
FROM tab_prior_data tpd
WHERE tab.version = tpd.version
AND tpd.prior_fieldA IS NOT NULL;

-- Optional: Temp tables auto-drop when your Redshift session ends, but you can manually clean up
DROP TABLE IF EXISTS tab_prior_data;

Why this works: #

Temporary tables are stored locally on each node, so joining against them avoids the distributed read/write conflict that comes with self-joining the original table during an update.

内容的提问来源于stack exchange，提问作者Eddie Hartman