UPDATE in postgresql with JOINS

Question

I have a form where some input has a value that is made with this query:

SELECT e.tree.nombre
FROM d.p
JOIN e.theme ON id = id_capa
LEFT JOIN e.tree ON e.theme.id_tree = e.tree.id
WHERE id_capa = 816

e and d are schemas. The id_capa = 816 is passed as an argument to the query from the form that I'm editing. It returns a value correctly. Now I want to edit that value on my form, so I need to UPDATE but I have multiple tables, I read that I can't do an UPDATE with JOINS, how should I do that UPDATE?

Do you need to update data located on multiple tables, or do you just need data from a secondary table to know which rows to update in a single table? — msg45f
– msg45f, Commented Oct 27, 2018 at 3:16

Ilya Konyukhov · Accepted Answer · 2018-10-27 03:09:41Z

1

In your original SQL query, the table d.p is not used, neither in SELECT nor in conditions. So it can be skipped; the query can be rewritten as:

SELECT e.tree.nombre
FROM e.theme
  LEFT JOIN e.tree ON e.theme.id_tree = e.tree.id
WHERE e.theme.id = 816

Since the query selects values from table joined with LEFT JOIN, the result can be NULL, ie joined record from e.tree table can be missed. In this case there is nothing to update.

Existing matching record can be updated with the query:

UPDATE e.tree
SET nombre = <NEW_VALUE>
FROM e.theme
WHERE e.theme.id = 816 AND e.theme.id_tree = e.tree.id

answered Oct 27, 2018 at 3:09

Ilya Konyukhov

2,7911 gold badge14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

UPDATE in postgresql with JOINS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related