Mysql out of range value adjusted for column


Im trying to copy a phone number feild away to a temp field

ALTER TABLE customers ADD tempF1 int(30) ;  <=== adding the temp field
UPDATE customers SET tempF1 = phone1;         <=== adding the phone numbers into temp feild

All my numbers get the error
'Out of range value adjusted for column tempF1' at row 14
'Out of range value adjusted for column tempF1' at row 15
'Out of range value adjusted for column tempF1' at row 16

and then all my mobile numbers LOOSE the leading '0'

eg

0417987654

becomes

417987654

what do i need to do to beable to store this correctly

Thanks

LVL 8
kingjelyAsked:
Who is Participating?
 
speight040Connect With a Mentor Commented:
Have you tried char(30) or varchar(30) a a datatype?
0
 
kingjelyAuthor Commented:

I have tried text

ALTER TABLE customers ADD tempF1 text(30) not null ;
UPDATE customers SET tempF1 = phone1;      

which works, but i get this little FILESEARCH icon in tempF1, and if i click on it, it takes me to a file to save too?

Anyone seen this before, please help!

Thankyou
0
 
pareshprajapatiConnect With a Mentor Commented:
For phone type of fields, you have to use varchar or char datatype.  This will help you to store data in formated way like nnn-nnn-nnnn or (nnn) nnn-nnnn or '0' prefixed or any format.

There is no advantage of keeping phone feilds numeric
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
kingjelyAuthor Commented:
Tis didnt really work, but  have tested on other tables and it worked  so thanks
0
 
theGhost_k8Database ConsultantCommented:
Well as such this is PAQed though, just FYI::
" If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column. "

Refer:
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
0
 
kingjelyAuthor Commented:

Hi sorry Kate, , There answer worked But just not for my case.. SO i assume its something I did wrong.

Thanks heaps for this info, trust me i read it all( or try to)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.