Hello all,

I'm not very proficient with MySQL syntax...
I was trying to do the following elementary thing but can't figure out the
right syntax:

UPDATE ImageList SET Avg=1 WHERE Name=abc SET Avg=5 WHERE Name=erg SET
Avg=17 WHERE Name=gvd

I tried with "()," around each SET/WHERE, I also tried to repeat the UPDATE
to no avail.

Thanks for any pointers.
--
Guillaume Dargaud
http://www.gdargaud.net/

Guillaume Dargaud schreef:
> Hello all,
>
> I'm not very proficient with MySQL syntax...
> I was trying to do the following elementary thing but can't figure out the
> right syntax:
>
> UPDATE ImageList SET Avg=1 WHERE Name=abc SET Avg=5 WHERE Name=erg SET
> Avg=17 WHERE Name=gvd
>
> I tried with "()," around each SET/WHERE, I also tried to repeat the UPDATE
> to no avail.
>
> Thanks for any pointers.

Easiest is probably this:

UPDATE ImageList SET Avg=1 WHERE Name='abc';
UPDATE ImageList SET Avg=5 WHERE Name='erg';
UPDATE ImageList SET Avg=17 WHERE Name='gvd';

> Easiest is probably this:
>
> UPDATE ImageList SET Avg=1 WHERE Name='abc';
> UPDATE ImageList SET Avg=5 WHERE Name='erg';
> UPDATE ImageList SET Avg=17 WHERE Name='gvd';

You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '; UPDATE
ImageList SET AvgColor=7891816 WHERE Name='03'; UPDATE ImageList SET ' at
line 1

I'm doing this in php and use something like:
$query.="UPDATE ImageList SET Avg=$Avg WHERE Name='$Name'; \n";
(also tried without the \n)

Hmmm, what's wrong ?
--
Guillaume Dargaud
http://www.gdargaud.net/

Guillaume Dargaud schreef:
>> Easiest is probably this:
>>
>> UPDATE ImageList SET Avg=1 WHERE Name='abc';
>> UPDATE ImageList SET Avg=5 WHERE Name='erg';
>> UPDATE ImageList SET Avg=17 WHERE Name='gvd';
>
> You have an error in your SQL syntax; check the manual that corresponds to
> your MySQL server version for the right syntax to use near '; UPDATE
> ImageList SET AvgColor=7891816 WHERE Name='03'; UPDATE ImageList SET ' at
> line 1
>
> I'm doing this in php and use something like:
> $query.="UPDATE ImageList SET Avg=$Avg WHERE Name='$Name'; \n";
> (also tried without the \n)
>
> Hmmm, what's wrong ?

The query I gave you however is correct.
You can see this if you run it in a tool like phpMyAdmin.
You are not using PHP correctly.

Don't use \n in your queries.
The PHP function mysql_query does not support multiple queries.
I don't mean to be rude, but I advise you to Google for a PHP/MySQL
tutorial.

Dutchie wrote:
> Guillaume Dargaud schreef:
>>> Easiest is probably this:
>>>
>>> UPDATE ImageList SET Avg=1 WHERE Name='abc';
>>> UPDATE ImageList SET Avg=5 WHERE Name='erg';
>>> UPDATE ImageList SET Avg=17 WHERE Name='gvd';
>>
>> You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use
>> near '; UPDATE ImageList SET AvgColor=7891816 WHERE Name='03'; UPDATE
>> ImageList SET ' at line 1
>>
>> I'm doing this in php and use something like:
>> $query.="UPDATE ImageList SET Avg=$Avg WHERE Name='$Name'; \n";
>> (also tried without the \n)
>>
>> Hmmm, what's wrong ?
>
> The query I gave you however is correct.
> You can see this if you run it in a tool like phpMyAdmin.
> You are not using PHP correctly.
>
> Don't use \n in your queries.
> The PHP function mysql_query does not support multiple queries.
> I don't mean to be rude, but I advise you to Google for a PHP/MySQL
> tutorial.

In addition to what Dutchie said, if all the queries need to be treated
as one, then change the table engine to innoDb and use a transaction.
You do that with:

mysql_query("BEGIN");
$result = mysql_query(the first one);
if ($result) $result = mysql_query(the second one);
if ($result) $result = mysql_query(the third one);
if ($result) mysql_query("COMMIT");
else mysql_query("ROLLBACK");

Guillaume Dargaud wrote:
> Hello all,
>
> I'm not very proficient with MySQL syntax...
> I was trying to do the following elementary thing but can't figure out the
> right syntax:
>
> UPDATE ImageList SET Avg=1 WHERE Name=abc SET Avg=5 WHERE Name=erg SET
> Avg=17 WHERE Name=gvd
>
> I tried with "()," around each SET/WHERE, I also tried to repeat the UPDATE
> to no avail.
>
> Thanks for any pointers.


Since you asked for a MYSQL solution (not PHP or some other language)

update ImageList
SET Avg= CASE
WHEN Name='abc' THEN 1
WHEN Name='erg' THEN 5
WHEN Name='gvd' THEN 17
ELSE Name !!just in case your select went wild
END
WHERE Name in ('abc','erg','gvd');


Some things to know:
The ELSE statement and WHERE clauses are optional - but I like using
them to ensure that only the correct rows are updated.

Learn the ANSI Standards and in particular the CASE statement - it is
very powerful and transportable across most database platforms. While
MYSQL has a lot of unique functions (as does MSSqlServer, Oracle and
most of the others) if you learn ANSI Standards, it [generally] does not
matter which database you run them on... (Using concat(...) for example
is MYSQL only...) and you would need to make some modifications to your
code should you decide to move it to a different platform.