From: | prevot morvan <prevotmorvan(at)yahoo(dot)fr> |
---|---|
To: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Issue with SQL query causing unintended consequences in database |
Date: | 2023-01-06 12:56:41 |
Message-ID: | 1815939917.12528321.1673009801704@mail.yahoo.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.htmlUPDATE: 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
From | Date | Subject | |
---|---|---|---|
Next Message | Pantelis Theodosiou | 2023-01-06 14:37:26 | Re: Issue with SQL query causing unintended consequences in database |
Previous Message | Masahiko Sawada | 2023-01-06 07:11:10 | Re: Segfault while creating logical replication slots on active DB 14.6-1 + 15.1-1 |