banco-de-dados – How to do an update with a join?

Question:

Hello, I'm new here in the forum and I need some help with the PostgreSQL DBMS.

My doubt is the following, in the following code used in the MySQL DBMS the execution happens perfectly, however 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 runs perfectly in Mysql but in PostgreSQl the following error appears:

ERROR: syntax error at or near "INNER".

can anybody help me?

Answer:

Postgres does not support the ansi-92 syntax of joins on update as MySQL in this your query must be done using the ansi-86 syntax which is the one where the join is done in the WHERE clause

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;