CODEWITHSUNDEEP
postgresql
pmiranda
pmiranda

Reputation: 8420

UPDATE in postgresql with JOINS

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?

Upvotes: 0

Views: 49

Answers (1)

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2781

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

Upvotes: 1

Related Questions