From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Vikas Sharma <shavikas(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: SQL query |
Date: | 2019-04-18 17:01:44 |
Message-ID: | 401f56ab-894c-dc50-bf16-08cfe262ddf7@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michel Pelletier | 2019-04-18 17:52:43 | Re: Method to pass data between queries in a multi-statement transaction |
Previous Message | David G. Johnston | 2019-04-18 17:00:08 | SQL query |