multiple updates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

multiple updates

Post 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?
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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

you can't update multiple tables in one query nor can you update multiple rows with different values.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

that's two queries...
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Update of Multiple Rows

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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;
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post by newmember »

may be you could use CASE WHEN THEN construct
Post Reply