Обсуждение: SQL query
Hi,
I have come across a query that a developer wrote to update a few rows in table, the query did update the two desired rows but also updated the rest of the table with the column value as 'false'.
Update tableA set col1 = null and col2 in (1,2);
The query updated col1 to null for the rows where col2 was either 1 or 2, rest of rows were also updated for col1 to 'false'.
The above was run without where clause.
Could the experts throw some light on this?
Regards
Vikas Sharma
On Thursday, April 18, 2019, Vikas Sharma <shavikas@gmail.com> wrote:
The above was run without where clause.
There was no where clause thus every row has to be updated by definition...null and bool evaluates to either false or null since if bool is false the null doesn’t matter and if bool is true the result is unknown from the null.
David J.
On 4/18/19 9:43 AM, Vikas Sharma wrote: > Hi, > > I have come across a query that a developer wrote to update a few rows > in table, the query did update the two desired rows but also updated the > rest of the table with the column value as 'false'. > > Update tableA set col1 = null and col2 in (1,2); > > The query updated col1 to null for the rows where col2 was either 1 or > 2, rest of rows were also updated for col1 to 'false'. > The above was run without where clause. > Could the experts throw some light on this? Hmm. What Postgres version? Assuming col1 is boolean, correct? My experimentation: create table up_test(id integer, col1 boolean, col2 integer); insert into up_test values (1, 't', 1), (2, null, 2), (3, 't', 4); update up_test set col1=null and col2 in(1,2); UPDATE 3 select * from up_test; id | col1 | col2 ----+------+------ 1 | | 1 2 | | 2 3 | f | 4 (3 rows) truncate up_test ; TRUNCATE TABLE insert into up_test values (1, 't', 1), (2, null, 2), (3, 't', 4); INSERT 0 3 update up_test set col1=(null and col2 in(1,2)); UPDATE 3 test_(postgres)# select * from up_test; id | col1 | col2 ----+------+------ 1 | | 1 2 | | 2 3 | f | 4 Looks to me it is seeing the and as part of an expression. > > Regards > Vikas Sharma -- Adrian Klaver adrian.klaver@aklaver.com
On 4/18/19 11:43 AM, Vikas Sharma wrote: > Hi, > > I have come across a query that a developer wrote to update a few rows in > table, the query did update the two desired rows but also updated the > rest of the table with the column value as 'false'. > > Update tableA set col1 = null and col2 in (1,2); > > The query updated col1 to null for the rows where col2 was either 1 or 2, > rest of rows were also updated for col1 to 'false'. > The above was run without where clause. On the QA server, right? -- Angular momentum makes the world go 'round.
Not able to produce this with PG 11.1
If col1 is any type other than boolean, the update statement fails in syntax.
If col1 is boolean, then it updated it correctly. In other words
update col1 = NULL
and col2 in (1,2)
is treated same as
update col1 = NULL
where col2 in (1,2)
Also I checked it on DB2 which rejected the UPDATE sql as bad syntax, as expected.
Oh wait. I see that it in both cases it did update correct target rows, but the value of col1 for non matching rows is different. In the first case (and col2), the non matching rows also got updated. So yes, same behavior like yours. > Sent: Thursday, April 18, 2019 at 2:36 PM > From: "Ravi Krishna" <srkrishna@myself.com> > To: "Vikas Sharma" <shavikas@gmail.com> > Cc: pgsql-general@lists.postgresql.org > Subject: Re: SQL query > > Not able to produce this with PG 11.1 > > If col1 is any type other than boolean, the update statement fails in syntax. > If col1 is boolean, then it updated it correctly. In other words > update col1 = NULL > and col2 in (1,2) > is treated same as > update col1 = NULL > where col2 in (1,2) > > Also I checked it on DB2 which rejected the UPDATE sql as bad syntax, as expected. > > > >
On 4/18/19 11:36 AM, Ravi Krishna wrote: > Not able to produce this with PG 11.1 > > If col1 is any type other than boolean, the update statement fails in syntax. > If col1 is boolean, then it updated it correctly. In other words > update col1 = NULL > and col2 in (1,2) > is treated same as > update col1 = NULL > where col2 in (1,2) The above is not the same format as OP's query: Update tableA set col1 = null and col2 in (1,2); > > Also I checked it on DB2 which rejected the UPDATE sql as bad syntax, as expected. > > > > -- Adrian Klaver adrian.klaver@aklaver.com
> The above is not the same format as OP's query: > > Update tableA set col1 = null and col2 in (1,2); I did include set in the sql. I typed it wrong here.