MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Speed Up Queries / Question No: 16

What is better query to update a table?

I have a table which contains ratings of buyers who are in thousands. I use a form with radio buttons to change rating for each buyer. After submitting I have an array where index is $buyer_id and value is $new_rating. Since, I change the rating more than times a month, it's not big deal, but I would like to know what is better solution. I have a three queries like below:
$SQL_1 = "UPDATE buyers SET rating='R'WHERE buyer_id=1 OR buyer_id=5 
OR buyer_id=8 OR buyer_id=23 OR ... OR buyer_id=1786";

mysql_query($SQL_1);

$SQL_2 = "UPDATE buyers SET rating='A' WHERE buyer_id=2 OR buyer_id=3
OR buyer_id=9 OR buyer_id=18 OR ... OR buyer_id=1823";

mysql_query($SQL_2);

$SQL_3 = "UPDATE buyers SET rating='NR' WHERE buyer_id=4 OR buyer_id=6
OR buyer_id=7 OR buyer_id=21 OR ... OR buyer_id=1824";

mysql_query($SQL_3);

or like below for each record:

mysql_query("UPDATE buyers SET rating='R' WHERE buyer_id=1"); 
mysql_query("UPDATE buyers SET rating='A' WHERE buyer_id=2");
mysql_query("UPDATE buyers SET rating='A' WHERE buyer_id=3");
mysql_query("UPDATE buyers SET rating='NR' WHERE buyer_id=4");

etc.

Or, is there is any better solution than above?

Answer No: 16

I would use three queries, as in your above first example, but use IN followed by a list of IDs that you construct. That would be easier to build than all of those OR conditions. Loop through the input something like this:
// Build three lists in this array, one for each rating ("A", "R", "NR")
$rating_list = array();

for ($i = 0; $i < count($buyer_id); $i++) {
$rating_list[$new_rating] .= "'" . $buyer_id . "',";
}

// Each list probably has one extra comma
$thislen = strlen($rating_list["A"]);

if ($thislen > 0) {
$rating_list["A"] = substr($rating_list["A"], 0, $thislen - 1);
}

$query = "UPDATE buyers SET rating='A' WHERE buyer_id
IN(${rating_list["A"]})";
... and so forth.

Related MySQL FAQs to the Above FAQ

How-to-get-all-dates-between-two-dates How to get all dates between two dates?

How-to-speed-up--multi-table-based-delete-query How to speed up multi table based delete query?

Why-my-update-query-works-slow-in-MySQL Why my update query works slow in MySQL?

What-is-query-cache-in-MySQL What is query cache in MySQL?

What-is-table-cache-in-MySQL What is table cache in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.