Postgres - Update table with subquery of same table says column reference is ambiguous

Question

I have a table which has columns such as

id | due_date | sent_at | due_days | calculate_due_date

some of the rows have null values for column calculated_due_date and I want to update such rows with a calculation using the existing columns in the table using a case statement. Below is the query that I have written but I think I am getting some alias wrong, I need some help and explanation to find the issue in this query, Thanks in advance.

UPDATE outgoinginvoice o2
  SET o2.calculated_due_date = calculated.calculated_due_date
  FROM (SELECT o.calculated_due_date,
    CASE
        WHEN o.due_date IS NULL THEN o.sent_at + CAST(o.due_days || ' days' AS INTERVAL)
        ELSE o.due_date
    END AS calculated_due_date
    FROM outgoinginvoice as o) as calculated
    WHERE o2.calculated_due_date IS NULL

The error I am receiving is,

column reference "calculated_due_date" is ambiguous

In the subquery you have two columns named ``calculated_due_date``! — sa-es-ir, Mar 29 '22 at 06:17
You have to add a join condition between the derived table (`calculated`) and the target table in your WHERE clause. — , Mar 29 '22 at 06:24

score 0 · Answer 1 · answered Mar 29 '22 at 06:29

0

UPDATE outgoinginvoice o2
  SET calculate_due_date = calculated.calculated_due_date
  FROM (select
    CASE
        WHEN o.due_date IS NULL THEN o.sent_at + CAST(o.due_days || ' days' AS INTERVAL)
        ELSE o.due_date
    END AS calculated_due_date
    FROM outgoinginvoice  o) calculated
WHERE o2.calculate_due_date IS NULL
returning *;

answered Mar 29 '22 at 06:29

jian

4,119
1
17
32

https://stackoverflow.com/questions/11369757/postgres-wont-accept-table-alias-before-column-name – jian Mar 29 '22 at 06:31

Postgres - Update table with subquery of same table says column reference is ambiguous

1 Answers1