How to Update in Two Table?

Question

I’m looking to make a update in two tables, postgres.

I’ve seen some posts using other banks and I believe I’m doing the same, maybe some particularity of the bank or is missing something. Update in two MYSQL tables

I have 3 tables

Tipo -> pkey id
Grupo -> pkey id e fkey(idTipo -> Tipo.id)
Produto -> pkey id e fkey(idGrupo -> Grupo.id)

Ex: int id = 10 ; boolean = false;

I want to use two SQL statements: the first will be a simple update that changes the visibility (Boolean) in the type table.

String sql = "update tipo set visibilidade = false where id = 10";

the second shall change the child groups of type 10 and successively the child products of the child groups of type '10' to 'false'.

I tried so:

UPDATE grupo g
    INNER JOIN produto p on g.id = p.idGrupo
    SET g.visibilidade = false, p.visibilidade = false
    WHERE g.idTipo=10;

ERROR: syntax error at or near "INNER" LINE 1: UPDATE group g INNER JOIN product p on

I tried otherwise, changing only one of the fields...

UPDATE produto
SET visibilidade = true
FROM grupo g INNER JOIN produto p on g.id = p.idGrupo
WHERE g.idTipo=10

The code runs...but changes all bank records... As long as you select...

select * 
FROM grupo g INNER JOIN produto p on g.id = p.idGrupo
WHERE g.idTipo=10;

works perfectly.

Answer 1

Try to use your syntax this way:

UPDATE grupo g
SET g.visibilidade = false, p.visibilidade = false
FROM 'sua tabela'
INNER JOIN produto p on g.id = p.idGrupo 
WHERE g.idTipo=10;