UPDATE using a loop or just one query?

Databases
1

Once a month I need to update a table and give every user a .99 credit. Would I need to loop through each record and perform the update based upon how much money they have and then add .99 to it? Or would I just be able to perform a single query that will update all rows and add .99 to their existing amount?

Table structure

CREATE TABLE IF NOT EXISTS `users_funds` (
  `uf_usr_id` int(11) unsigned NOT NULL,
  `uf_payment_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`uf_usr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Loop through each record

foreach($users as $user) {
	$sql = mysql_query("
	UPDATE 
		users_funds 
	SET 
		payment_amount= payment_amount + .99 
	WHERE 
		usr_id = $user['usr_id'] 
	LIMIT 1
	");
}

One query to update all

$sql = mysql_query("
UPDATE 
	users_funds 
SET 
	payment_amount = payment_amount + .99
");
2

one query to update them all at once

why would you even think of doing it one user at a time? :wink:

these types of queries are easy to test, you know – and test UPDATE queries are also very useful in reminding you about the value of table backups

:slight_smile:

3

Yeah they are. I just wanted assurance that this wasn’t a stupid thing to do. :slight_smile:

Thanks!

4

Like you said your self they all get the .99 added to their amount. It would be something else if there would be exeptions

5

I wanted to make sure that the calculations would be performed correctly for each row as opposed to wiping all the existing data and everybody ending up with only .99 in their account. :slight_smile:

6

That would be something hey :slight_smile: I don’t think they would have appreciated that :wink: