UPDATE with 2 Joins POSTGRESQL

Question

I would like to ask for help with an update on Posrgresql

I need to update the value of a column in a table, but that goes through conditions of 2 tables. I tried the code:

update proced_conv set proced_conv.vl_unitario = (proced_conv.vl_unitario_bras * c.ajuste_porcent / proced_conv.fator_ajuste)
from proced_conv
   inner join convenio c on c.cd_convenio = proced_conv.cd_convenio 
   inner join proced_conv_det pcd on pcd.cd_proced_conv = proced_conv.cd_proced_conv
   where convenio.cd_convenio in (94, 146) and pcd.cd_proced = 28021609

However the following error appears:

ERROR: table name "proced_conv" specified more than Once SQL state: 4271

2

Answer 1

Postgresql does not use this type of syntax. Try:

WITH t AS (
    SELECT proced_conv.cd_convenio, proced_conv.vl_unitario_bras * c.ajuste_porcent / proced_conv.fator_ajuste AS val
    FROM proced_conv
       INNER JOIN convenio c on c.cd_convenio = proced_conv.cd_convenio 
    INNER JOIN proced_conv_det pcd on pcd.cd_proced_conv = proced_conv.cd_proced_conv
   WHERE convenio.cd_convenio in (94, 146) AND pcd.cd_proced = 28021609)
UPDATE proced_conv SET vl_unitario = t.val WHERE cd_convenio = t.cd_convenio;