SQL Multiple Update Statements

Question

I need to update 40 rows in a database, starting at the point where the userID is a match. I do not want to have to determine the row number because eventually this database will be huge.

What I would like to do is simply say:

"Update these the next 40 rows with my array where userID = myUserID"

Here is what I have:

<?php
// connect to the database and select the correct database
$con2 = mysql_connect("localhost","Database","Password");
if (!$con2)
{
   die('Could not connect: ' . mysql_error());
}

mysql_select_db("ifaves_code", $con2);

$i = 0;
while ($i < 40) {

//cycle through the array
$cycleThrough2 = $updatedUserNames[$i];

//$query = "UPDATE tblUserLinks SET `URLName` = '$cycleThrough2' WHERE userID = '" . $mainUID . "' LIMIT 1";

mysql_query($query) or die ("Error in query: $query");
++$i;

}
mysql_close();
?>

The variable $mainUID is being set correctly, the problem I'm having is it appears there are no updates taking place in the database.

How can I alter my existing code to receive my desired behaviour?

When you say "next 40 rows" what do you mean? Next in what ordering? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 14, 2011 at 13:31

Michael Berkowski · Accepted Answer · 2011-08-14 13:26:32Z

3

I don't suppose it's because you have your query building statement commented out...

//$query = "UPDATE tblUserLinks SET `URLName` = '$cycleThrough2' WHERE userID = '" . $mainUID . "' LIMIT 1";

If this is just the result of debugging and it wasn't working before that, you must call mysql_error() right after mysql_query() to see why it is failing.

$result = mysql_query($query);
if (!$result) echo mysql_error();

Also, you are using LIMIT 1 at the end, but the userID never changes in the WHERE clause. Therefore, you are updating the same row over and over 40 times on each loop. What you need is a way in your WHERE clause to identify rows which have already been modified, and exclude them. Otherwise, the same row (first match) will always be caught by the LIMIT 1 and updated.

edited Aug 14, 2011 at 13:26

answered Aug 14, 2011 at 13:20

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike Over a year ago

ooops that was from a test even with it uncommented it does not work. Sorry about that haha.

Michael Berkowski Over a year ago

@Mike see addition above about updating the same row over and over

Mike Over a year ago

Thank you Michael, I was hoping it would cycle through the rows where it found the first userID. I'll get the row number and cycle through that. Thank you for your help.

ypercubeᵀᴹ Over a year ago

@mMike: Do you mean you want the row with userID = '$mainUID' and the next 39 (bigger than $mainUID) userIDs?

Collectives™ on Stack Overflow

SQL Multiple Update Statements

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related