Jump to content

Archived

This topic is now archived and is closed to further replies.

realjumper

Update query syntax

Recommended Posts

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

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.