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
