How to do this particular update + join in PostgreSQL 9.2.13?

Question

Alright. I know that this use case already has a question devoted to it here but I have tried just about any syntax there and I am getting desperate.

This is my query:

with ids as (
  select t.id ntid, ot.id otid, p.id npid, op.id opid, ot.company_id ocid, t.company_id ncid
  from product_template t
  inner join product_template ot on t.multicompany_old_id = ot.id
  inner join product_product p on p.product_tmpl_id = t.id
  inner join product_product op on op.product_tmpl_id = ot.id
)
update ol
set product_id = i.npid
from sale_order_line ol
inner join sale_order o on ol.order_id = o.id
inner join product_product p on ol.product_id = p.id
inner join ids i on p.id = i.opid and o.company_id = i.ncid;

Variations I have tried:

update [sale_order_line] ol
set [ol.]product_id = i.npid
from...

update sale_order_line ol
set ol.product_id = i.npid
inner join ....

update sale_order_line ol
set product_id = i.npid
from sale_order o on ol.order_id = o.id
inner join product_product p on ol.product_id = p.id
inner join ids i on p.id = i.opid and o.company_id = i.ncid
where ol.order_id = o.id;

Nothing works. Could someone help me? I guess a solution for the following simplified case also suffices:

update T
set T.value = J.value
from some_table T
inner join joined_table J on J.some_table_id = T.id;

Iurii Tkachenko · Accepted Answer · 2016-10-14T09:24:42.930

1

Try this one:

WITH ids AS (
    SELECT
        t.id ntid,
        ot.id otid,
        p.id npid,
        op.id opid,
        ot.company_id ocid,
        t.company_id ncid
    FROM product_template t
        INNER JOIN product_template ot ON t.multicompany_old_id = ot.id
        INNER JOIN product_product p ON p.product_tmpl_id = t.id
        INNER JOIN product_product op ON op.product_tmpl_id = ot.id
)
UPDATE sale_order_line
SET product_id = i.npid
FROM sale_order_line ol
    INNER JOIN sale_order o ON ol.order_id = o.id
    INNER JOIN product_product p ON ol.product_id = p.id
    INNER JOIN ids i ON p.id = i.opid AND o.company_id = i.ncid
WHERE sale_order_line.id = ol.id;

The idea is that table that you want to update is kind of outside of the FROM part, and to make an update right you need to mark in which cases you need to update data with WHERE part.

edited Oct 14 '16 at 09:24

answered Dec 09 '15 at 21:35

Iurii Tkachenko

3,106
29
34

This is mysql-syntax. In postgres you dont have to add the target for the update to the from list. – wildplasser Dec 09 '15 at 21:58
@wildplasser so you say `where` can be omitted? – Iurii Tkachenko Dec 09 '15 at 22:15
No, `from sale_order_line ol` can be omitted, but that will cause some reshuffling of terms to be needed. – wildplasser Dec 09 '15 at 22:17
@wildplasser cool, I'll try tomorrow and tune my answer, thanks! – Iurii Tkachenko Dec 09 '15 at 22:31
Perhaps you can leave this answer as it is, and do the tuning in wildplasser's answer, and then (since i have the tables here) I'll test it until the errors disappear – littlegreen Dec 09 '15 at 22:40
Putting the target table into the `FROM` clause of an UPDATE statement is almost always an error. – Oct 14 '16 at 09:27

wildplasser · Answer 2 · 2015-12-09T23:39:16.460

0

You don't need to add/join the target table in the update to the FROM // 'JOIN' list; it is already in the range table.

WITH ids AS (
  SELECT t.id ntid, ot.id otid, p.id npid
        , op.id opid, ot.company_id ocid, t.company_id ncid
  FROM product_template t
  JOIN product_template ot ON t.multicompany_old_id = ot.id 
  JOIN product_product p ON p.product_tmpl_id = t.id
  JOIN product_product op ON op.product_tmpl_id = ot.id 
)
UPDATE sale_order_line ol -- <<-- target table has an alias
SET product_id = i.npid   -- <<-- product_id SHOULD NOT use this alias
FROM ids i 
JOIN sale_order o ON o.company_id = i.ncid;
JOIN product_product p ON p.id = i.opid
WHERE ol.product_id = p.id   -- <<-- target table is linked    
AND ol.order_id = o.id       -- <<-- to the source(s) here
        ;

edited Dec 09 '15 at 23:39

answered Dec 09 '15 at 21:42

wildplasser

43,142
8
66
109

Error in query: ERROR: invalid reference to FROM-clause entry for table "ol" LINE 11: join product_product p on ol.product_id = p.id HINT: There is an entry for table "ol", but it cannot be referenced from this part of the query. – littlegreen Dec 09 '15 at 21:45
Yes, you'll need to reshuffle the joined tables a bit. BRB. – wildplasser Dec 09 '15 at 21:48
Still gives the same error. And the semicolons are in the wrong place too. Plus, Yuri beat you to it :-) His query works. But thanks a lot – littlegreen Dec 09 '15 at 21:51

How to do *this particular* update + join in PostgreSQL 9.2.13?

2 Answers2

How to do this particular update + join in PostgreSQL 9.2.13?