On Fri, Jan 6, 2023 at 1:46 PM prevot morvan <
prevotmorvan@yahoo.fr> wrote:
>
> Dear PostgreSQL bug team,
>
> I am writing to report an issue I encountered when running an SQL query on my database.
>
> I ran the following query:
>
> UPDATE
> nf_job
> SET
> status = 'TO_DO',
> error_message = NULL
> FROM
> nf_job n
> JOIN formality f ON n.formality_id =
f.id> WHERE
> f.liasse_number IN ('J00011156148', 'J00011416104');
>
>
> To my surprise, this query erased ALL of the status and error_messages in the nf_jobs table, rather than just changing two lines as I had intended.
>
> Upon reviewing the documentation for SELECT and UPDATE at the following links:
>
> SELECT:
https://www.postgresql.org/docs/current/sql-select.html> UPDATE:
https://www.postgresql.org/docs/current/sql-update.html>
> I noticed that the SELECT statement allows for the use of "JOIN", but the UPDATE statement does not. This leads me to believe that it should have been a syntax error to include "JOIN" in an UPDATE statement. However, no syntax error was thrown and the query seemed to attempt to run anyway, resulting in the unintended consequences described above.
>
> I would be grateful if the team could take a look into this issue and let me know if there is any way to prevent this from happening in the future.
>
> Thank you in advance for your help.
>
> Sincerely,
> Émile PRÉVOT
This is not a bug. The statement did what it should do. Notice that in
https://www.postgresql.org/docs/current/sql-update.html , it mentions:
> from_item
>
> A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement; for example, an alias for the table name can be specified.
Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).
So the statement did a self-join of the updated table with itself.
You probably wanted to run this query instead (without repeating the updated table in the FROM clause and moving the ON condition to the WHERE clause):
UPDATE
nf_job
SET
status = 'TO_DO',
error_message = NULL
FROM
formality f
WHERE
f.liasse_number IN ('J00011156148', 'J00011416104')
AND nf_job.formality_id =
f.id ;
