CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
littlegreen
littlegreen

Reputation: 7420

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

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;

Upvotes: 1

Views: 229

Answers (2)

Iurii Tkachenko
Iurii Tkachenko

Reputation: 3189

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.

Upvotes: 1

wildplasser
wildplasser

Reputation: 44250

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
        ;

Upvotes: 0

Related Questions