Okay, had to check MySQL docs, and fortunately MySQL behaves well with the RAND( ) function.
So it's really simple:
First of all, clean out the field you want to randomly set:
Code:
UPDATE distributionqueue SET Subject=NULL;
Find out how many records are in that table:
Code:
SELECT COUNT(*) FROM distributionqueue
record that number (e.g., in a variable in PHP/ASP/whatever you use)
and then simply do:
Code:
UPDATE distributionqueue SET Subject='XYZ'
ORDER BY RAND()
LIMIT (thatCount * percentage)
In other words, if you have 800 records in the table, and you want to set 50% of them to "GPS - Upto 70% off", you would do
Code:
UPDATE distributionqueue SET Subject='GPS - Upto 70% off'
ORDER BY RAND()
LIMIT 400
Now, for the next subject, say "GPS on sale" where you wanted 30% of the records to be changed:
Code:
UPDATE distributionqueue SET Subject='GPS on sale'
WHERE Subject IS NULL
ORDER BY RAND()
LIMIT 240 // 240 is 30% of 800
Obviously, you could set up a stored procedure to do all this, excepting only the setting all the Subject fields to NULL to get started.
This is off the top of my head, utterly untested (I don't happen to have MySQL on this computer right now):
Code:
delimiter //
CREATE PROCEDURE setRandomSubject(
subj VARCHAR(255),
percentage FLOAT )
BEGIN
SELECT COUNT(*) INTO allCount FROM distributionqueue;
UPDATE distributionqueue SET Subject = subj
WHERE Subject IS NULL
ORDER BY RAND()
LIMIT percentage * allCount;
END;
delimiter ;
I *think* that works. If not, if the LIMIT can't be used like that with UPDATE (but I've seen it used similarly), then we could do it another way:
Code:
UPDATE distributionqueue SET Subject = subj
WHERE emailID IN (
SELECT emailID FROM distributionqueue
WHERE Subject IS NULL
ORDER BY RAND()
LIMIT percentage * allCount
);
As I said, untested, but give the idea a shot.
Update Command
