Using Left Outer Join in Redshift Update Query result in ERROR: Target table must be part of an equi-CodePudding

I have this query and running it resulted in the error

SQL Error [XX000]: ERROR:
Target table must be part of an equijoin predicate

UPDATE sandbox.f_contribution
SET lpct = NVL(l.percentage, 0)
FROM sandbox.f_contribution AS f
LEFT OUTER JOIN sandbox.f_contribution_last AS l
    ON f.year = l.year AND f.location = l.location AND f.category = l.category
    WHERE f.year = 2020;

The docs say that LEFT OUTER JOIN is not supported and it suggest to "use a subquery that clearly separates the join conditions from the criteria that qualify rows for updates", so I tried modifying the query as such but the same error persists:

UPDATE sandbox.f_contribution
SET lpct = NVL(c.percentage, 0)
FROM (
select l.percentage
from sandbox.f_contribution AS f
LEFT OUTER JOIN sandbox.f_contribution_last AS l
    ON f.year = l.year AND f.location = l.location AND f.category = l.category
    ) c WHERE f_contribution.year = 2020;

How should I modify the same query to run it in Redshift?

CodePudding user response：

You've read up on what was the issue in your first SQL. Your second is missing the necessary information to perform the UPDATE, specifically in the WHERE clause.

Your subquery produces a set of rows with one column called percent. Your target table has a column called lpct that get set to a value from the subquery (or zero if NULL), but which subquery value? How is the UPDATE suppose to apply these values to the target?

I suspect you need your WHERE clause to have some alignment test that clears this confusion up. The WHERE clause is basically how to join the data in the FROM clause with the data in the subquery.

Guessing at your intent is risky but you might be intending to do:

UPDATE sandbox.f_contribution
SET    lpct = Nvl(c.percentage, 0)
FROM   sandbox.f_contribution_last AS c
WHERE  f_contribution.year = c.year
       AND f_contribution.location = c.location
       AND f_contribution.category = c.category
       AND f_contribution.year = 2020;