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@xxxxxxxxxxx
