Re: SQL query

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