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(dot)klaver(at)aklaver(dot)com