Jump to content


Update query syntax

  • Please log in to reply
3 replies to this topic

#1 realjumper

  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 15 May 2006 - 04:22 AM


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

#2 jeremywesselman

  • Members
  • PipPipPip
  • Advanced Member
  • 154 posts
  • LocationIndependence, KY

Posted 15 May 2006 - 05:36 AM

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--]

#3 btherl

  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 15 May 2006 - 07:24 AM

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

If your username field is a varchar, you can do

UPDATE users SET username = CONCAT('s', username)

First, you should try

SELECT username, CONCAT('s', username) FROM users

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

#4 realjumper

  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 15 May 2006 - 10:57 AM

Thanks btherl and jeremywesselman for taking the time :-)


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..

....etc etc, and the staff are....

....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.


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.


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users