Rename a Username 1

[ThankYou2]

im trying to rename a user name in my data base and i used this syntex below in my database sql, but getting error
i looked/searched here but didnt seem to see what i want and yes im a newbee please help with full syntex thanks

update table set Username=replace(Username,'roy1','roy2') where instr(Username,'roy1')!=0;

#1064 - 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 'table set Username=replace(Username,'roy1','roy2') where inst' at line 1

 

[vacunita]

what exactly are you trying to do?

If all you want to do is change one username to another a simple set is all you need.

update table set Username='Roy2' where Username='Roy1'

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ThankYou2]

thanks for your reply, im trying to change a name in the database username from roy1 to roy2 and i tryed what you sudjested but still get error, i did do this once a few years back but im getting old an cant remmber how i did it.


UPDATE TABLE SET Username = 'roy1' WHERE Username = 'roy1'

#1064 - 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 'table set Username='roy1' where Username='roy2'' at line 1

 

[vacunita]

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

I assumed "table" was your table's name.

So in other words:

UPDATE mytable SET set Username='Roy2' where Username='Roy1'

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ThankYou2]

thanks that did it sorry for my newbyest

 

[ThankYou2]

1 more question if you dont mind
can i use this on several tables

UPDATE mytable and mytable and mytable and mytable and mytableSET set Username='Roy2' where Username='Roy1'

 

[vacunita]

Yes, separate each table with a comma:

UPDATE mytable1, mytable2, mytable3 SET table1.filename='sopmevalue', mytable2.otherfield='value', mytable3.yetanotherfield='value'WHERE...

MYSLQ's online manual. Update Syntax entry with lots of examples. just scroll down.

http://dev.mysql.com/doc/refman/5.0/en/update.html

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ThankYou2]

thanks for your time an replys, im going to put this query in a form so i can do all the tables at one time.

have a nice thanks giving

 

[ThankYou2]

Hi sorry for asking again but i seem to of mis under stood mutible tables

i made a form with two drop down menus an they both echo selected names to page, an in address bar,then it calls a new page that updates names.

this works great below

$sql="UPDATE NatHotLap set Username='$id ' where Username='$id2'";
-------------------------------------------------
But when i try this below for multible tables i get erro
and all tables are in same database, what am im doing wrong please help, thanks


$sql="UPDATE NatHotLap,NatTRT,SXHotLap,SXTRT,Torny_1_score,Torny_2_score,Torny_3_score set NatHotLap.Username='$id ',NatTRT.Username='$id ',SXHotLap.Username='$id ',SXTRT.Username='$id ',Torny_1_score.Username='$id ',Torny_2_score.Username='$id ',Torny_3_score.Username='$id ' where Username='$id2'";

 

[vacunita]

Posting the errors you are getting would help. But right off the bat, I'm assuming you are getting a "USERNAME is ambiguous" error.

You need to be specific. Which 'Username' should be equal to $id2?

$sql="UPDATE NatHotLap,NatTRT,SXHotLap,SXTRT,Torny_1_score,Torny_2_score,Torny_3_score set NatHotLap.Username='$id ',NatTRT.Username='$id ',SXHotLap.Username='$id ',SXTRT.Username='$id ',Torny_1_score.Username='$id ',Torny_2_score.Username='$id ',Torny_3_score.Username='$id ' where [red]NatHotLap.[/red]Username='$id2' and [red]NatTRT.[/red]Username='$id2'...";

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ThankYou2]

The WHERE Username=$1d2 is the $id2 but i just did this and it works, maybe not the right way but it worked

$sql="UPDATE NatHotLap set Username='$id ' where Username='$id2'";
$result=mysql_query($sql);
$sql1="UPDATE NatTRT set Username='$id ' where Username='$id2'";
$result1=mysql_query($sql1);
$sql2="UPDATE SXHotLap set Username='$id ' where Username='$id2'";
$result2=mysql_query($sql2);
$sql3="UPDATE SXTRT set Username='$id ' where Username='$id2'";
$result3=mysql_query($sql3);
$sql4="UPDATE Torny_1_score set Username='$id ' where Username='$id2'";
$result4=mysql_query($sql4);
$sql5="UPDATE Torny_2_score set Username='$id ' where Username='$id2'";
$result5=mysql_query($sql5);
$sql6="UPDATE Torny_3_score set Username='$id ' where Username='$id2'";
$result6=mysql_query($sql6);

 

[vacunita]

That works yes, as you are effectively issuing separate queries for each Update.

What I was suggesting above is to be specific. You are Updating 7 different tables. each with a field named Username. But you never specify of those fields in those tabes which is supposed to be equal to $id2.

My example should have been enough to understand that you needed to specify the table name in the where clause for it to work.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.