Page 1 of 1

multiple updates

Posted: Tue May 09, 2006 6:59 pm
by s.dot
Say I have an array like the following

Code: Select all

Array
(
    [1] => 1000
    [2] => 5000
    [3] => 2500
)
Where there could be potentially a thousand or more elements.

What I need to achieve is something like this:

Code: Select all

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?

Posted: Wed May 10, 2006 2:51 am
by GM
Hi,

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.

Posted: Wed May 10, 2006 3:48 pm
by s.dot
Hmm, so its not possible to do a multiple update in one query?

Posted: Wed May 10, 2006 3:52 pm
by Burrito
you can't update multiple tables in one query nor can you update multiple rows with different values.

Posted: Thu May 11, 2006 4:38 am
by dibyendrah
Did you mean this :

Code: Select all

mysql> update test3 set y=1000 where x=1;  update test3 set y=2000 where x=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
:?

Regards,
Dibyendra

Posted: Thu May 11, 2006 9:30 am
by Burrito
that's two queries...

Update of Multiple Rows

Posted: Thu May 11, 2006 9:55 pm
by tr0gd0rr
Array
(
[1] => 1000
[2] => 5000
[3] => 2500
)
"UPDATE `list` SET `rank` = '$key' WHERE `field` = '$value'"
There are a few work arounds that may or may not be faster depending on your needs. Consider the following. They all worked in my test.

Replace Into

Code: Select all

REPLACE INTO list
(field,rank)
VALUES
(1000,1),
(5000,2),
(2500,3);
--
-- user must have insert and delete priviledges

Insert On Duplicate Key Update

Code: Select all

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

Inner Join Update

Code: Select all

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

Posted: Fri May 12, 2006 1:32 am
by s.dot
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.

Posted: Fri May 12, 2006 3:38 am
by Weirdan
Burrito wrote:you can't update multiple tables in one query
MySQL manual wrote: Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]
Burrito wrote:nor can you update multiple rows with different values.
You can, as long as those values are produced by single expression. Example:

Code: Select all

update `tablename` set `columnname`=`columnname`+1;

Posted: Fri May 12, 2006 9:44 am
by newmember
may be you could use CASE WHEN THEN construct