Not done this for a while with moving house and looks like it will still be difficult with BT messing up the telephone lines in the building, but O2 are providing no end of support. So thought I would do a short post in my lunch hour at work (actually taking a “break” for once) and do a short post.
One of the MySQL DBA colleagues asked me the other day if I could do a random update on a table, apart from the fact I could not understand at the time why you would want to randomly update a number of rows and not have an idea what you have done till you interrogate the data again, that’s another article for the what if I am allowed to publish it (doesn’t that sound intriguing!). The main IBM page in the DB2 Information Centre is here on the RAND() OLAP function.
So what’s the best way to update say 1000 rows in a 10000 row table randomly in DB2, I am not going to cover the way to do it on MySQL but heres how you could do it:
UPDATE <SCHEMA>.<TABLE> ST
SET <COLUMN> = SOMEVALUE
WHERE ST.<IDENTIFIER COLUM> = (SELECT <IDENTIFIER COLUM>
FROM <SCHEMA>.<TABLE>
ORDER BY RAND()
FETCH FIRST 1000 ROWS ONLY)
This code will randomly update 1000 rows, and will choose a different 1000 each time. You could but RAND() to other uses to create a random number but you will need to remember that it only generates a number between 1 and 0
So that’s it, one of my shorter ones.