Update query syntax
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.
Posted 15 May 2006 - 05:36 AM
Posted 15 May 2006 - 07:24 AM
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.
Posted 15 May 2006 - 10:57 AM
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....
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.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users