update query append number in start of all rows

Question

Post reply

update query append number in start of all rows

masood24

Grasshopper

Points: 10
More actions
August 16, 2008 at 3:10 pm

#103561

Hello,
Is there any way to update all the rows in a table table1 with scnario
suppose i have 100 records with a column phone_numbers
I want to add 91 in all the start of every phone number
***mAsOoD*** O
|
/\

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Update Dbo.Table1 UPDATE dbo.location SET local_Phone = '91'+local_phone

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

bitbucket (8/16/2008)
Update Dbo.Table1 UPDATE dbo.location SET local_Phone = '91'+local_phone

Just to be sure, I'd add a check to make sure the numbers haven't already been updated...

UPDATE dbo.Table1

SET Local_Phone = '91' + Local_Phone

WHERE Local_Phone NOT LIKE '91%'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

masood24 Grasshopper Points: 10 More actions · Answer 3

hello dear 1st thank you very much for help but I have tried the query but it is not working message is successful with 1 row changed but actually nothing is happening my query is like under

update table_users set mail_number='1'+mail_number where id='12';

I am using mysql .

***mAsOoD*** O
|
/\

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 4

masood24 (8/17/2008)
hello dear 1st thank you very much for help but I have tried the query but it is not working message is successful with 1 row changed but actually nothing is happening my query is like under
update table_users set mail_number='1'+mail_number where id='12';
I am using mysql .

How are you determining that it is not working? Have you selected the row with id = 12 and verified whether or not the update changed the value?

What were you expecting to happen that did not happen?

Note: this forum is for Microsoft SQL Server 2005. Although we can help with general SQL questions, what works for Microsoft SQL Server may not work correctly for another product. The above should work the same in both MySQL and MSSQL.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

masood24 (8/17/2008)
hello dear 1st thank you very much for help but I have tried the query but it is not working message is successful with 1 row changed but actually nothing is happening my query is like under
update table_users set mail_number='1'+mail_number where id='12';
I am using mysql .

First, don't call me "dear".

Second, your original request was talking about phone numbers, not "mail_numbers.

Third, this is an SQL Server forum... the UPDATE in MySQL may be a bit different.

Fourth, please read and head the link in my signature below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)