Update on 80% of Records

[dai.hop]

Hi all,

I have a table presently containing 40,905 rows. I would like to run a query that will update the 'marker' field on 80% of these records. Something like...

UPDATE comments SET marker = 'Foo' WHERE id IN (SELECT RANDOM 80% FROM comments)

Is this possible? If so, is it possible to do without crippling the database server?

Thanks!

dai.hop

[ssystems]

Whan database are you using? 40k rows is nothing unless each row is 1GB each.

[dai.hop]

It's MySQL version 4.1.11

[ssystems]

Ugggh. Don't you think its time to upgrade?

Here is on MSSQL

update comments set marker = 'foo' where id in (select top (FLOOR(4 * (SELECT COUNT(*) FROM comments ) / 5)) id from comments ORDER BY NEWID())


I think you can do something similar to that in 5.0.7 using Prepare. Check the concept and just translate it to the syntax of your database.

[dai.hop]

Thanks for your reply. I just tried running this query and received the following error message:

#1305 - FUNCTION db_name.TOP does not exist

Do you know why I'm receiving this message?

[ssystems]

Thanks for your reply. I just tried running this query and received the following error message:



Do you know why I'm receiving this message?

The function does not exist.

[criterion9]

TOP is MSSQL only, use LIMIT for MYSQL instead.