How To Update Random ID to a table in mysql?

Question

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

Answer 1

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.

Answer 2

update Table1 t1
Inner join
(Select * from Table1 where City = 'Bandung' 
 Order by Rand()limit 1) t2
on t1.ID = t2.ID
Set t1.Prize = 'Watch';

Sample fiddle

Answer 3

UPDATE table-name SET Prize = "Watch" 
    WHERE City = "Bandung" 
    AND ID = ROUND((9-7)*RAND()) + 7

Answer 4

You want to only update 1 value at a time right?

In order to UPDATE existing values, you will need to generate a table with the possible values and a selectable value, something like a counter, which I added into the @table. The reason for this is you might not always have sequential IDs with no prizes. Take 'Semarang' as an example of this. So we force a sequence by using IDENTITY.

We will then select a random number against the IDENTITY column in order to always select an existing value.

DECLARE @prizetable TABLE (ID INT, City VARCHAR(50), Prize VARCHAR(50));
INSERT INTO @prizetable VALUES
(1,'Surabaya', 'HP'),(2,'Surabaya', 'Watch' )
,(3,'Surabaya', 'Bag'),(4,'Semarang','')
,(5,'Semarang','Watch'),(6,'Semarang','')
,(7,'Bandung',''),(8,'Bandung','')
,(9,'Bandung',''),(10,'Bandung','')
,(11,'Bandung',''),(12,'Bandung',''),(13,'Bandung','');

DECLARE @CityToDo VARCHAR(50) = 'Bandung';
/*Lets do a driver table, adding a counter so that you can do a RAND select against existing numbers*/
DECLARE @needsprize TABLE (Counter INT IDENTITY(0,1), ID INT);
INSERT INTO @needsprize
SELECT p.ID
FROM @prizetable p
WHERE (p.Prize IS NULL OR LEN(p.Prize) < 1)
AND City = @CityToDo;

/*This will give your RAND a ceiling, else you will update nothing most of the time*/
DECLARE @RANDceiling INT = (SELECT MAX(Counter) FROM @needsprize);
DECLARE @RANDnumber INT = (SELECT ROUND(RAND ()*(@RANDceiling) ,0));

UPDATE p
SET p.Prize ='Watch This'
FROM @prizetable p
INNER JOIN @needsprize np ON np.ID = p.ID 
WHERE (p.Prize IS NULL OR LEN(p.Prize) < 1)
AND City = @CityToDo
AND np.Counter = @RANDnumber;

SELECT * 
FROM @prizetable

EDIT

There is no simple function to to what you want, unless you don't mind (0 row(s) affected) all the time.

Let me explain more. You want to look at 'Semarang' for this.

ID  City        Prize
4   Semarang    
5   Semarang    Watch
6   Semarang    

You need to look at ID 4 and 6 in order to give a prize. 5 already has a prize.

You SELECT RANDOM NUMBER it returns a 157862, no updates will be made as the number is outside of the range.

You SELECT RANDBETWEEN 4 and 6 it returns 5, no updates will be made.

So now you take the IDs that need updating and you add a sequence number to them. So lets to a direct Update, you will still need some type of staging table, so lets use a CTE

/*This will simulate your table*/
DECLARE @prizetable TABLE (ID INT, City VARCHAR(50), Prize VARCHAR(50));
INSERT INTO @prizetable VALUES
(1,'Surabaya', 'HP'),(2,'Surabaya', 'Watch' )
,(3,'Surabaya', 'Bag'),(4,'Semarang','')
,(5,'Semarang','Watch'),(6,'Semarang','')
,(7,'Bandung',''),(8,'Bandung','')
,(9,'Bandung',''),(10,'Bandung','')
,(11,'Bandung',''),(12,'Bandung',''),(13,'Bandung','');

DECLARE @CityToDo VARCHAR(50) = 'Bandung';


WITH TempPrizeTable AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber, p.ID
    FROM @prizetable p
    WHERE (p.Prize IS NULL OR LEN(p.Prize) < 1)
    AND City = @CityToDo
) 
UPDATE p
SET p.Prize ='Watch This'
FROM @prizetable p
INNER JOIN TempPrizeTable tp ON tp.ID = p.ID
WHERE tp.RowNumber = (SELECT ROUND(RAND ()*(SELECT COUNT(*) FROM TempPrizeTable),0));

SELECT * FROM @prizetable