Jump to content


Photo

Update query syntax


  • Please log in to reply
3 replies to this topic

#1 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 15 May 2006 - 04:22 AM

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

#2 jeremywesselman

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

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

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 15 May 2006 - 10:57 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users