How To Update Random ID to a table in mysql?

I have table like this:

ID | City     | Prize |
----------------------------
1  |Surabaya  | HP
2  |Surabaya  | Watch 
3  |Surabaya  | Bag
4  |Semarang  |
5  |Semarang  |Watch
6  |Semarang  |
7  |Bandung   |
8  |Bandung   |
9  |Bandung   |

My Question is How to creat Update Query for Fill The Prize column where City = '(already in settings)' AND ID = Random.

ex: I want Update to fill Watch in City = 'Bandung' But ID = 'random' maybe (7,8,9).

I Hope you know What I Mean.

Im Very Apreciated your Advice

Thanks

The simple solution is to materialize the result of the query that identifies the row to update, and fetch that result with a scalar subquery...

UPDATE table_name
   SET prize = 'Watch'
 WHERE id = (SELECT * 
               FROM ( SELECT id 
                        FROM table_name 
                       WHERE City = 'Bandung' 
                         AND (Prize != 'Watch' OR Prize IS NULL)
                       ORDER BY RAND()
                       LIMIT 1 ) dt1
             );

This selects a random id FROM the table from the desired set of rows, materializes that as a derived table with the alias dt1 with one row and one column (the id of the new winner), and then selects that id using a subquery and uses it in the outer where clause to update the row.

There's no chance of 0 rows affected unless everybody already has a watch because we selected the "randomly first" id from among the list of potential winners.

StackExchange Database Administrators Q#54015, answer score: 3