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? 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]
Re: select and update field in one query
- 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]
Re: select and update field in one query
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]
Re: select and update field in one query
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]
