FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


How to update multiple rows in mysql with one query?





Nicholai
Hello

I want to update multiple rows in a database.
Ofcourse I can update them all with a individual query, but if there are over 3000 query's to update, I think that [s]mysql[/s] PHP will time-out. Cool

So..
Is it possible to update multiple rows, with only ONE query? And if so HOW?

Something like the following returns in an error:
Code:
UPDATE `jos_weblinks` (
SET id =1 WHERE id =15
),
(
SET title = 'www.php.net' WHERE id =2
)


Thanks in advance Very Happy
ncwdavid
Well I have never been in a situation like that or delt with one like that but if its all the same stuff you need to put in you could do a while() function but if you post back explain in more detail then I could probably help you out.
mathiaus
Just use the one query where the id equals something OR something else eg
Code:
UPDATE `tablename` SET `title`='Mr' WHERE `id=1 OR `id`=3 OR `id=9
Mgccl
mathiaus wrote:
Just use the one query where the id equals something OR something else eg
Code:
UPDATE `tablename` SET `title`='Mr' WHERE `id=1 OR `id`=3 OR `id=9


that could be a lot...
can you pass it like WHERE id IN(1,3,9)
Nicholai
Ok ill try to explain myself better:

I would like to have something like this:

Code:
UPDATE `jos_jms`
SET blocked = '123,255,1024' WHERE userid = 992,
SET blocked = '992, 456,225' WHERE userid = 123;


As you can see I
- Update the same table
- update the same key
BUT
- for each row the value are different
- the rows who need to be updated are given, so I do not want to update all the rows.

I hope you ppl understand it more now Cool

And again, thanks in advance Wink
kv
Code:
UPDATE `jos_jms`
SET blocked = '123,255,1024' WHERE userid = 992,
SET blocked = '992, 456,225' WHERE userid = 123;


The above cannot be achieved in single sql statement. But you can use prepared statement. Create statement once, set values and execute query for different values.

http://www.petefreitag.com/item/356.cfm
Nicholai
kv wrote:
Code:
UPDATE `jos_jms`
SET blocked = '123,255,1024' WHERE userid = 992,
SET blocked = '992, 456,225' WHERE userid = 123;


The above cannot be achieved in single sql statement. But you can use prepared statement. Create statement once, set values and execute query for different values.

http://www.petefreitag.com/item/356.cfm


Ok, ill do it trough deleting them first and insert them back then with the new values.
Trough this way I only need 2 query's. Thats still beter than individual query's Very Happy
Mgccl
kv wrote:
Code:
UPDATE `jos_jms`
SET blocked = '123,255,1024' WHERE userid = 992,
SET blocked = '992, 456,225' WHERE userid = 123;


The above cannot be achieved in single sql statement. But you can use prepared statement. Create statement once, set values and execute query for different values.

http://www.petefreitag.com/item/356.cfm


Cool, but does that must use mysqli extension? Sad
Nicholai
Mgccl wrote:
kv wrote:
Code:
UPDATE `jos_jms`
SET blocked = '123,255,1024' WHERE userid = 992,
SET blocked = '992, 456,225' WHERE userid = 123;


The above cannot be achieved in single sql statement. But you can use prepared statement. Create statement once, set values and execute query for different values.

http://www.petefreitag.com/item/356.cfm


Cool, but does that must use mysqli extension? Sad


Dunno know what you mean, but I do the following now:
Code:
$db->setQuery( 'DELETE FROM `#__jms_settings` WHERE userid IN (' .implode( ',', $delete). ')');
if (!$db->query()) {
   JError::raiseError( 500, $db->stderr());
}
$db->setQuery( 'INSERT INTO `#__jms_settings` VALUES ' .implode( ",", $insert). ';');
if (!$db->query()) {
   JError::raiseError( 500, $db->stderr());
}

PS. The $db object is from Joomla 1.5
bgillingham
I believe that you could pass the multple updates as a single string value using a single call to mysql_query(), but you have to separate lines with a semicolon as well as have "UPDATE 'tablename'" at the beginning of each line.
Code:

  $sql = "UPDATE `jos_jms`
    SET blocked = '123,255,1024' WHERE userid = 992;
    UPDATE `jos_jms`
    SET blocked = '992, 456,225' WHERE userid = 123;
    UPDATE `jos_jms`
    SET blocked = '000, 111,222' WHERE userid = 999;";
  $result = mysql_query($sql);

Note that there are double-quotes around the whole SQL.
kv
Nicholai wrote:
kv wrote:
Code:
UPDATE `jos_jms`
SET blocked = '123,255,1024' WHERE userid = 992,
SET blocked = '992, 456,225' WHERE userid = 123;


The above cannot be achieved in single sql statement. But you can use prepared statement. Create statement once, set values and execute query for different values.

http://www.petefreitag.com/item/356.cfm


Ok, ill do it trough deleting them first and insert them back then with the new values.
Trough this way I only need 2 query's. Thats still beter than individual query's Very Happy


Not really. If you are deleting records and inserting them again instead of updating, performance takes a hit. And you might get into other integrity issues if you are using myisam tables.
mahta-m
See these pages:
http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/
http://stuporglue.org/update-multiple-rows-at-once-with-different-values-in-mysql/
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69902

you can use this query:

UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
Related topics
Help with mysql query
An Example to mySQL class
mySQL Query/PHP code - get the highest value...
What is wrong with this MySQL query?
mySQL Query Problem
[Edit] mySQL Count
Help importing CSV file into MySQL database
PHP MySQL Question
mysql insert into multiple tables at once?
whats wrong with this mysql query?
Looping through multiple mysql results
MySQL Query help
MySQL Query Cache
mysql query based on 2 fields using 3 different tables
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.