Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
I think you want AND (Prize != 'Watch' OR Prize IS NULL) – ypercubeᵀᴹ Nov 27 '13 at 16:14
@ypercube if the column is nullable, you're absolutely correct. I made the assumption that it wasn't, based on what looked like empty strings in the question, but the additional test is valid with either option in the column definition, so +1 ...updated. – Michael - sqlbot Nov 27 '13 at 16:55
@Michael - sqlbot Great. thats What I Need. :) Thanks – Cromastone Nov 28 '13 at 1:09
Any update order by rand() or id=rand() is replication unsafe if you have statement based replication. Hope your are not having that level complex stuff. But if you have a replication, do a select for update followed by update statement. – georgecj11 Nov 28 '13 at 16:22
@cjg +1 that's true if you you're using STATEMENT mode; however, if ROW or MIXED then this query will be logged correctly since using RAND() will trip the internal logic to flag the query unsafe for statement logging and cause any query involving it to be logged as a row event. Using binlog_format=STATEMENT, it will throw an "unsafe" warning, which some clients ignore. Statement mode has so many potential problems that I never use it and didn't stop to consider that it is still, almost inexplicably, the default. – Michael - sqlbot Nov 28 '13 at 18:15
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

share|improve this answer
UPDATE table-name SET Prize = "Watch" 
    WHERE City = "Bandung" 
    AND ID = ROUND((9-7)*RAND()) + 7
share|improve this answer
Makasih Mas, 0 row affected. ID nya tu detek berdasarkan Kota misal bandung 6-9 maka yg dirandom itu ya 6-9. kalau Surabaya misal 1-5 ya yang dirandom 1-5. bisa nggak Mas? – Cromastone Nov 27 '13 at 4:57

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));
(1,'Surabaya', 'HP'),(2,'Surabaya', 'Watch' )
,(3,'Surabaya', 'Bag'),(4,'Semarang','')

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
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));

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;

FROM @prizetable


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));
(1,'Surabaya', 'HP'),(2,'Surabaya', 'Watch' )
,(3,'Surabaya', 'Bag'),(4,'Semarang','')

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

WITH TempPrizeTable AS
    FROM @prizetable p
    WHERE (p.Prize IS NULL OR LEN(p.Prize) < 1)
    AND City = @CityToDo
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
share|improve this answer
Thanks For Your Explanation, but I Still Not UnderStand. I Just Need maybe A function or something that Can random the ID before Update it. – Cromastone Nov 27 '13 at 5:59
Please see EDIT, you cannot simply use a function. A function like RAND will return a number that will be out of the range of your list of IDs. – Adrian Sullivan Nov 27 '13 at 6:34
Thanks For the Explanation. It Help me So much. :) – Cromastone Nov 27 '13 at 8:36
The question is tagged with "mysql". How can IDENTITY, CTEs and ROW_NUMBER() help - from which MySQL has none? – ypercubeᵀᴹ Nov 27 '13 at 14:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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