Re: select and update field in one query

[Michael Stassen]

I'm assuming you want the rows with the top 1000 usage values.  Why not just 
do it in 1 UPDATE statement?

  UPDATE yourtable SET checked = 1 ORDER BY usage DESC LIMIT 1000;
This should work for any mysql 4.x.x, according to the manual 
<http://dev.mysql.com/doc/mysql/en/UPDATE.html>.

Michael
Rhino wrote:
----- Original Message ----- 
From: "darren" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 28, 2004 11:42 AM
Subject: select and update field in one query


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?
If you are selecting the 1000 rows so that you can display them and then
update them to keep your database up-to-date, you can't do both in one
statement. A SELECT statement can only display data and an UPDATE can only
update data; an UPDATE cannot display rows *and* change values on those
rows.
If you are selecting the rows simply so that you can update them but don't
display them, the update should be straightforward if you are using a
version of MySQL that supports subqueries (V4.1.x). Something like this:
update my_table
set checked = 1
where primary_key in
    (select primary_key
    from my_table
    where
    order by usage
    limit 1000)
This query probably won't work as is; I'm not that fluent on MySQL's brand
of SQL yet. Other posters on this group can help you fine tune it so that it
works.
Rhino


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]