UPDATE with 2 SETs

hello,

so i want to merge these 2 queries:

$query1 = mysql_query("UPDATE act SET from='$new_un' WHERE to='$un_curr'");
$query2 = mysql_query("UPDATE act SET to='$new_un' WHERE from='$un_curr'");

how can i do it?

edit : if im in a wrong section please move it.

$query1 = mysql_query("UPDATE act SET `from`='$new_un', `to`='$new_un' WHERE `to`='$un_curr'");

notice also the usage of backtics for your fields "from" and "to"... both are MYSQL rserved words

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

 

$query1 = mysql_query("UPDATE act SET `from`='$new_un', `to`='$new_un' WHERE `to`='$un_curr'");

 

notice also the usage of backtics for your fields "from" and "to"... both are MYSQL rserved words

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

Thanks for the reply but the query doesnt seem to work.. so i have to add these backtics in all my queries?will check the link , thanks.

Thanks for the reply but the query doesnt seem to work.. so i have to add these backtics in all my queries?will check the link , thanks.

What happens?

Thanks for the reply but the query doesnt seem to work.. so i have to add these backtics in all my queries?will check the link , thanks.

 

What happens?

it sents from and to WHERE `to`='$un_curr' which is not what i want..

i want it to from='$new_un' WHERE to='$un_curr'

and            to='$new_un' WHERE from='$un_curr'

would this work?

$query1 = mysql_query("UPDATE act SET `from`='$new_un', `to`='$new_un' WHERE `to`='$un_curr',`from`='$un_curr'");

so basically the first `from`='$new_un' goes with -> WHERE `to`='$un_curr'

and                          `to`='$new_un' goes with -> WHERE `from`='$un_curr'

I think you want

$query1 = mysql_query("UPDATE act SET `from`='$new_un', `to`='$new_un' WHERE `to`='$un_curr' OR `from`='$un_curr'");

That will update both the 'from' and 'to' values on any row that has either 'to' or 'from' = $un_curr.

If you only want to update 'from' where 'from' = $un_curr, and only update 'to' where 'to' = $un_curr, then you do have to do it in two separate queries. (Well, you _can_ do it in one, but let's just say it's a lot easier to do it in two)

on my table there might be some to columns NULL so i just want to update the from from un_curr to new_un ...

which causes problems... i can stick around with my 2 queries.. i dont wanna bother you much ... just want the merged query to do the same job as the 2 that i have on my first post.

thanks.

@fxuser:  my fault... I read your post incorrectly

try this

UPDATE act
  SET   `from` = IF (`to` = '$un_curr', '$new_un', `from`),
        `to`   = IF (`from` = '$un_curr', '$new_un', `to`);

@fxuser:  my fault... I read your post incorrectly

 

try this

UPDATE act
  SET   `from` = IF (`to` = '$un_curr', '$new_un', `from`),
        `to`   = IF (`from` = '$un_curr', '$new_un', `to`);

that did the job:

UPDATE act
  SET   `from` = IF (`from` = '$un_curr', '$new_un', `from`),
        `to`   = IF (`to` = '$un_curr', '$new_un', `to`);

thanks.