There is an initial table (fruits):

ID | TITLE | COLOR | COUNT ___|_____________________________ 1 | Banana | Yellow | 0 2 | Lemon | Yellow | 0 3 | Apple | Green | 0 4 | Orange | Orange | 0 5 | Pear | Yellow | 0

How do I change the COUNT value when prompted:

 "SELECT title FROM fruits WHERE color = 'Yellow' ORDER BY RANDOM() LIMIT 2";

Those. two randomly selected fruits must change by one:

 ID | TITLE | COLOR | COUNT ___|_____________________________ 1 | Banana | Yellow | 1 2 | Lemon | Yellow | 0 3 | Apple | Green | 0 4 | Orange | Orange | 0 5 | Pear | Yellow | 1

It turned out to write the following:

 "UPDATE fruits SET count = (count + 1) WHERE title IN (SELECT title FROM fruits WHERE color = 'Yellow' ORDER BY RANDOM() LIMIT 2)"

But with this request, I can not work with the selected entries.

  • change in the table? or just as a result of select? - Novitskiy Denis
  • @NovitskiyDenis you need to select several records and, for example, display them on the screen, but their count value in the table should change, I will not display and use it - slippyk
  • then these are 2 different operations in sqlite; this can be solved only by 2 different operations - first update, then select. as an option - add a label to the table for updated entries and select the latest updated entries by it - Novitskiy Denis
  • @NovitskiyDenis thanks, I can't do without two operations, I have already thought about it too - slippyk

1 answer 1

For myself, I decided this in the following way:

  • I first perform a query not only for names, but also for their ID ;

"SELECT id, title FROM fruits WHERE color = 'Yellow' ORDER BY RANDOM() LIMIT 2";

  • I save a list of IDs, for example, IDs = "1, 2" ;
  • I use it to update the data in the second request.

"UPDATE fruits SET count = (count + 1) WHERE id IN (" + IDs + ");"