BUG #16462: Update Statement destructive behaviour with joins

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: srivastava(dot)adi24(at)gmail(dot)com
Subject: BUG #16462: Update Statement destructive behaviour with joins
Date: 2020-05-26 11:28:28
Message-ID: 16462-7160938c9c2989d9@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16462
Logged by: Aditya Srivastava
Email address: srivastava(dot)adi24(at)gmail(dot)com
PostgreSQL version: 9.6.2
Operating system: MAC OSX
Description:

Let's say I have a table orange and a temp table temp, and i want to update
the records after joining the columns in temp table. I used the following
syntax to update the records which ended up updating the entire table
"orange".

UPDATE orange
SET fruit_flag = 'okay'
FROM temp as t
INNER JOIN portal_users p on t.fruit_id = p.fruit_id
WHERE p.id = '123';

I know that the correct syntax should be the following but judging from the
destructive nature of this query i honestly feel we should throw validation
error if the above syntax is not correct.

UPDATE orange
SET fruit_flag = 'okay'
FROM temp t
WHERE t.fruit_id = orange.fruit_id

Thanks in advance.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2020-05-26 12:48:19 Re: BUG #16462: Update Statement destructive behaviour with joins
Previous Message Peter Eisentraut 2020-05-26 10:44:25 Re: