How do I update with a Join?

Question

Hello, I’m new to the forum and I need some help with SGBD Postgresql.

My doubt 'and the following, in the following code used in DBMS Mysql the execution happens perfectly, by'em in Postgresql shows an error.

Mysql code:

 UPDATE userT
    INNER JOIN empresa ON userT.idEmp = empresa.idEmp
SET userT.telefone = '15253485',
    empresa.cargaTrab = 12
WHERE idUser = 1;

The above mentioned code performs perfectly in Mysql but in Postgresql appears the following error:

ERROR: syntax error at or near "INNER".

Could someone help me?

Answer 1

Postgres does not support ansi-92 syntax of joins in the update as Mysql in your query should be done using the ansi-86 syntax which is the one where the merge is done in the clasp WHERE

The consultation should look like this:

 UPDATE userT SET
    userT.telefone = '15253485',
    empresa.cargaTrab = 12
 FROM empresa
    WHERE userT.idEmp = empresa.idEmp
    AND idUser = 1;

Answer 2

with t as (
  -- Any generic query which returns rowid and corresponding calculated values
  select t1.id as rowid, f(t2, t2) as calculatedvalue
  from table1 as t1
  join table2 as t2 on t2.referenceid = t1.id
)
update t1
set value = t.calculatedvalue
from t
where id = t.rowid

Answer 3

I do as follows via select on the condition I need.

update ESTOQUE
set QTDE=100
where CODMARCA in (select CODIGO from marca where nome = CONDIÇÃO)

Answer 4

Hello I always do it this way, ride the sql for instruction select Example:

SELECT
       c.nome,
       tc.descricao
from cliente c 
left join tipo_cliente tc on tc.id_tipo_cliente  = c.id_tipo_cliente
    where
tc.id_tipo_cliente=13;

---

Warning: The following operation is not supported by Postgresql(Update Syntax)

Based on select:

update cliente c
    left join tipo_cliente tc on tc.id_tipo_cliente  = c.id_tipo_cliente
set tc.descricao='TESTE'
where tc.id_tipo_cliente=13;

With the select done and working, copy and delete the from select information back. Add update and after joins and before the where the set.