foreach($array AS $key=>$value){
mysql_query("UPDATE `list` SET `rank` = '$key' WHERE `field` = '$value'");
}
Instead of looping through and doing a possible thousand or more queries, is there a way to get this into one huge update query?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
I don't think so. It would be no problem if you were updating multiple rows to the same value, you would just use WHERE field IN ('value1', 'value2'...), but since both the field value and the row identifier are changing, I can't see a shorter way to do it from an SQL point of view.
Hmm, so its not possible to do a multiple update in one query?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
INSERT INTO list
(field,rank)
VALUES
(1000,1),
(5000,2),
(2500,3)
ON DUPLICATE KEY UPDATE
rank=VALUES(rank);
--
-- `field` must be a unique or primary key
CREATE TEMPORARY TABLE tmp_list (
field INT(10) UNSIGNED NOT NULL PRIMARY KEY,
rank INT(10) UNSIGNED NOT NULL
);
INSERT INTO tmp_list
(field,rank)
VALUES
(1000,1),
(5000,2),
(2500,3);
UPDATE list
INNER JOIN tmp_list ON tmp_list.field = list.field
SET list.rank = tmp_list.rank;
--
-- user must have priviledges to create temporary table
REPLACE INTO list
(field,rank)
VALUES
(1000,1),
(5000,2),
(2500,3);
This looks like what I'd be interested in, since I could hit the server with one massive query, instead of a thousand or more single queries.
I dunno which would be more server intensive, but this seems to be simpler.
I'll have to research REPLACE INTO.
Thanks.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.