----- 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]
