Is there a way to set data in multiple records in one query?

[WetWired]

In MySQL, is there an efficient way to set a field to a specified list of values in a corresponding list of where conditions (based on the primary key), or do I have to do an individual UPDATE query for each record?

[Dean C]

Sorry you've totally lost me. Can you perhaps give an example of what you're trying to do ?

[rake]

you could write a function to do that, but you'd still end up with more queries.

$list[0] = "...";
$list[0] = "...";

$where[0] = "...";
$where[1] = "...";

for($i = 0; $i < sizeof($list); $i++)
{
mysql_query("UPDATE table SET $list[$i] WHERE $where[$i]");
}

[WetWired]

Originally Posted by rake

you could write a function to do that, but you'd still end up with more queries.

$list[0] = "...";
$list[0] = "...";

$where[0] = "...";
$where[1] = "...";

for($i = 0; $i < sizeof($list); $i++)
{
mysql_query("UPDATE table SET $list[$i] WHERE $where[$i]");
}

Hmm. Is it more efficient (or even supported) to send multiple queries at the same time, separated by semicolons?

[Marco van Herwaarden]

With this loop you ARE sending multiple queries.

Better to do like Dean asked and give an examplke of what you try to do.

[rake]

Originally Posted by MarcoH64

With this loop you ARE sending multiple queries.

Better to do like Dean asked and give an examplke of what you try to do.

there's no way around sending multiple queries, if i understand correctly what wetwired wants to do.

Hmm. Is it more efficient (or even supported) to send multiple queries at the same time, separated by semicolons?

If you can send multiple queries in one go (never tried it), it would be more efficient than what i wrote, since you wouldn't lose time looping through that for statement.

[WetWired]

Originally Posted by rake

there's no way around sending multiple queries, if i understand correctly what wetwired wants to do.




If you can send multiple queries in one go (never tried it), it would be more efficient than what i wrote, since you wouldn't lose time looping through that for statement.

What I mean is that I would loop and accumulate the UPDATE queries in a string separated by semicolons, then send them all in the same mysql_query call. Does this do anything to minimize overhead?

Also, people are always talking about the cost of a hack in terms of queries, but I can easily construct a query that will take 10 times longer to execute than another. What gives? I hardly believe that the majority of users concerned with such things aren't using mysql as a library, so I don't understand why the number of queries should be more important than the time spent executing them.

[Dean C]

Hmmm, well by the sounds of things what you're doing doesn't sound very efficient either way. As I said before why not tell us exactly what you're trying to do then we can give tips. It's hard to offer advice without an example

[rake]

Originally Posted by WetWired

What I mean is that I would loop and accumulate the UPDATE queries in a string separated by semicolons, then send them all in the same mysql_query call. Does this do anything to minimize overhead?

I know that's what you meant. But i doubt it will have any impact.

Also, people are always talking about the cost of a hack in terms of queries, but I can easily construct a query that will take 10 times longer to execute than another. What gives? I hardly believe that the majority of users concerned with such things aren't using mysql as a library, so I don't understand why the number of queries should be more important than the time spent executing them.

EXACTLY. I'd rather have 10 fast queries than one big slow one. People generally worry too much about the query count.

[filburt1]

Originally Posted by WetWired

In MySQL, is there an efficient way to set a field to a specified list of values in a corresponding list of where conditions (based on the primary key), or do I have to do an individual UPDATE query for each record?

Look at REPLACE INTO syntax for updating if a primary key exists, inserting otherwise.