How to do an update with a join?

Question

How to do an update with a join?

Navigation

#1 by (6 votes)
#2 by (0 votes)
#3 by (0 votes)

5

Hello, I'm new to the forum and need help with PostgreSQL DBMS.

My question is the following, in the following code used in the MySQL DBMS the execution happens perfectly, because in PostgreSQL it 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 code mentioned above performs perfectly in Mysql but in PostgreSQl the following error appears:

ERROR: syntax error at or near "INNER".

Could anyone help me?

sql-update postgresql

asked by anonymous 25.03.2016 / 23:59

3 answers

0

Hello I always do the following, I set the sql to select statement 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;

Based on select gero update:

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, it copies and clears the select information from back to back. Add update and after joins and before where set .

22.09.2016 / 15:11

0

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

15.12.2016 / 19:59

Difference speed in HD Sata / SSD What is the best place to store the connection string? [closed]

score 6 · Accepted Answer

Postgres does not support ansi-92 syntax of joins in the update as MySQL in this query should be done using ansi-86 syntax that is where the join is made in the WHERE

The query should look like this:

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