Update query syntax

[realjumper]

Hi,

I have about 600 users in my database. All the usernames are numbers, and all the numbers begin with a '2'. Now the boss in his infinite wisdom wishes for all the usernames to have an 's' in front of the '2'. Is it possible to make a query for this?....I imagine it is, but I can't get the syntax right.

Thanks for any help.

regards Neil

[jeremywesselman]

Show us what you have right now and we'll see if we can help you modify it to meet your needs. If you show us what you have, we will have a better understanding of how your table is built and how you are going about it.

[!--coloro:#990000--][span style=\"color:#990000\"][!--/coloro--]Jeremy[!--colorc--][/span][!--/colorc--]

[btherl]

It sounds like you want username to be converted like "234" => "s234" ?

If your username field is a varchar, you can do

[code]UPDATE users SET username = CONCAT('s', username)[/code]

First, you should try

[code]SELECT username, CONCAT('s', username) FROM users[/code]

to make sure that it is doing what you want it to.

[realjumper]

Thanks btherl and jeremywesselman for taking the time :-)

jeremywesselman.........

The table is pretty standard......id (auto increment), username, email, first_name, last_name etc etc

The usernames are in the number format for students, and text for staff....so the username table is varchar. The students usernames are currently in the following format..

2094746
2066551
2056427
....etc etc, and the staff are....

jbrown
ismith
wperkins
....etc etc.

I can't use id in the WHERE condition because students and staff don't follow the id in cronological order. So the where condition would have to be something like 'WHERE username=20*'. I'm sure I can figure that part out, but it's how to add the 's' before the '2' that has me mystified! This will run once only in cocoa mysql (I'm on a MAC) as there will be no need to worry about it again, once the students usernames have a 's' before their username.


btherl......

That sounds like the right track for me to follow, thanks. It's 11pm here at home so I'll investigate more at work in the morning and see what I can put together.

Thank you.

Neil