0

I want to select the lowest value in the 'n' sql column and increment it by 1. If the lowest value is present in multible rows, then I want to choose among those rows at random. For instance, in the example table below where the lowest number is 0 I want to randomly choose between the rows where ID = 1, 2, or 3.

ID n
1 0
2 0
3 0
4 1
5 2

The code below will increment all three rows where n = 0. How do I randomly select just 1? I use Adminer as database.

$sql = "UPDATE studycondition SET n=n +1 WHERE n=(SELECT MIN(n) FROM studycondition)";
Sarah
  • 23
  • 4

2 Answers2

1

use

"UPDATE studycondition SET n=n +1 WHERE n=(SELECT MIN(n) FROM studycondition) limit 1"
Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17
1

Add limit in your inner query and update by id

UPDATE studycondition SET n = n + 1 
WHERE id=(SELECT id FROM studycondition order by n asc limit 1)
Ravi Hirani
  • 6,511
  • 1
  • 27
  • 42