2

I create the new table and insert 10 records into the new table as below:

userdb=> CREATE TABLE test_tbl(ID INT PRIMARY KEY, STR VARCHAR);
CREATE TABLE

userdb=> do $$
begin
for r in 1..10 loop
insert into user1.test_tbl(id, str) values(r, r);
end loop;
end;
$$;
DO
userdb=> select * from test_tbl;
 id | str 
----+-----
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
  6 | 6
  7 | 7
  8 | 8
  9 | 9
 10 | 10
(10 rows)

userdb=> \set id floor(random()*(10-1+1))+1
userdb=> select :id;
 ?column? 
----------
        7
(1 row)

userdb=> select :id;
 ?column? 
----------
        1
(1 row)

userdb=> select :id;
 ?column? 
----------
        4
(1 row)

userdb=> select :id;
 ?column? 
----------
        7
(1 row)

userdb=> UPDATE test_tbl SET str=111 WHERE ID=:id;
UPDATE 1
userdb=> UPDATE test_tbl SET str=222 WHERE ID=:id;
UPDATE 1
userdb=> UPDATE test_tbl SET str=333 WHERE ID=:id;
UPDATE 2
userdb=> select * from test_tbl;
 id | str 
----+-----
  2 | 2
  4 | 4
  5 | 5
  6 | 6
  7 | 7
  8 | 8
  1 | 111
  3 | 222
  9 | 333
 10 | 333
(10 rows)

Anyone could let me know why the last update will update multiple data?

1 Answer 1

1

Your query is

UPDATE test_tbl SET str=333
WHERE ID = floor(random()*(10-1+1))+1;

Now random() is a volatile function, that is, it can return different results if it is called several times in a single SQL statement. So the expression floor(random()*(10-1+1))+1 is evaluated for each row of the table. As a consequence, it can happen that no row at all or more than one row are found that match the condition.

If you want random() to be evaluated only once, the classical trick is to use an uncorrelated subquery, which is evaluated only once:

UPDATE test_tbl SET str=333
WHERE ID = (SELECT floor(random()*(10-1+1))+1);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.