"plan should not reference subplan's variable" error

Question

I need to update 2 columns in a table with values from another table

UPDATE transakcje t SET s_dzien = s_dzien0, s_cena = s_cena0
    FROM
        (SELECT c.price AS s_cena0, c.dzien AS s_dzien0 FROM ciagle c 
            WHERE c.dzien = t.k_dzien ORDER BY s_cena0 DESC LIMIT 1) AS zza;

But I got an error:

plan should not reference subplan's variable.

DB structure is as simple as possible: transakcje has k_dzien, k_cena, s_dzien, s_cena and ciagle has fields price, dzien.

I'm running PostgreSQL 9.3.

Edit

I want to update all records from transakcje. For each row I must find one row from ciagle with same dzien and maximum price and save this price and dzien into transakcje.

In ciagle there are many rows with the same dzien (column is not distinct).

You should explain what your statement is meant to achieve in plain English. What are you trying to update exactly? Your code is ambiguous. Do you want to update a *single* row or one row with the highest `c.price` per `k_dzien` in `transakcje`? — Erwin Brandstetter, Nov 01 '13 at 01:54

score 3 · Answer 1 · edited May 23 '17 at 11:53

Problem

The form you had:

UPDATE tbl t
SET    ...
FROM (SELECT ... WHERE col = t.col LIMIT 1) sub

... is illegal to begin with. As the error message tells you, a subquery cannot reference the table in the UPDATE clause. Items in the FROM list generally cannot reference other items on the same level (except with LATERAL in Postgres 9.3 or later). And the table in the UPDATE clause can never be referenced by subqueries in the FROM clause (and that hasn't changed in Postgres 9.3).

Even if that was possible the result would be nonsense for two reasons:

The subquery with LIMIT 1 produces exactly one row (total), while you obviously want a specific value per dzien:

one row from ciagle with same dzien
Once you amend that and compute one price per dzien, you would end up with something like a cross join unless you add a WHERE condition to unambiguously join the result from the subquery to the table to be updated. Quoting the manual on UPDATE:

In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Solution

All of this taken into account your query could look like this:

UPDATE transakcje t
SET    s_dzien = c.dzien
     , s_cena  = c.price
FROM  (
    SELECT DISTINCT ON (dzien)
           dzien, price
    FROM   ciagle
    ORDER  BY dzien, price DESC
   ) c
WHERE t.k_dzien = c.dzien
AND  (t.s_dzien IS DISTINCT FROM c.dzien OR
      t.s_cena  IS DISTINCT FROM c.price)

Get the highest price for every dzien in ciagle in a subquery with DISTINCT ON. Details:
Select first row in each GROUP BY group? Like @wildplasser commented, if you all you need is the highest price, you could also use the aggregate function max() instead of DISTINCT ON:
```
...
FROM  (
   SELECT dzien, max(price) AS price 
   FROM   ciagle
   GROUP  BY czien
   ) c
...
```
transakcje ends up with the same value in s_dzien and k_dzien where related rows are present in ciagle.
The added WHERE clause prevents empty updates, which you probably don't want: only cost and no effect (except for exotic special cases with triggers et al.) - a common oversight.

It would be good to explain why the OP's approach gave the error it did, as well as providing a working solution. — IMSoP, Nov 02 '13 at 12:07
@Erwin: IMHO given `WHERE t.k_dzien = c.dzien` , the `AND (t.s_dzien IS DISTINCT FROM c.dzien` term can never be true, so the where clause can be reduced to `WHERE t.k_dzien = c.dzien AND t.s_cena IS DISTINCT FROM c.price` . BTW: Having `MAX(price)` in de subquery would be clearer (and less dependent on the postgres dialect). — wildplasser, Nov 03 '13 at 12:08
@wildplasser: Note the difference between `k_dzien` and `s_dzien`. Not sure if the table design makes sense, but while it is that way, the `WHERE` condition makes sense. You are right about `max()`, but often people show simplified code and if we need more columns from `ciagle`, `max()` cannot replace `DISTINCT ON`. — Erwin Brandstetter, Nov 03 '13 at 12:32
I might have been confused by the columns names. (and maybe the OP might have been confused by his own naming, too ) — wildplasser, Nov 03 '13 at 12:56
@IMSoP: You are right, of course. I was a bit frustrated with the lack of information in the Q. Added a detailed explanation. — Erwin Brandstetter, Nov 03 '13 at 13:02

"plan should not reference subplan's variable" error

1 Answers1

Problem

Solution