I would personally do it in three statements. The first one creates a
temporary table from the results of the (SELECT) below. The second is the
update with a JOIN to the temporary table. The third drops the temporary
table. Doing it that way avoids the need to scroll through your recordset
client-side.
I think this could(might) work for you as an all-in-one statement (not
tested):
UPDATE tablename INNER JOIN (SELECT tablename.ID from tablename WHERE
usage='somevalue' ORDER BY id LIMIT 1000) as rows_to_update ON
rows_to_update.id = tablename.id
SET checked=1
As I said, it's not tested. It also relies on the "derived table"
(anonymous view) feature of MySQL which may not be available in your
version.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"darren"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
box.com> cc:
Fax to:
06/28/2004 11:42 Subject: select and update field in
one query
AM
Hi all,
I need to select the top 1000 records based on the "usage" field and update
the "checked" field to '1'.
I am currently doing it in 2 statements; i.e. select first then update from
a MYSQL_ROW array. How do I do it in just a single statement?
Please advice on a better way. Also...will doing this in one statement be
more efficient from the server perspective?
Thanks in advance
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
