SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    help with simple Alter command

    Hello,

    What is wrong with this simple Alter command:
    alter table articles add column test int(2) default = 0;
    or
    alter table articles add column test int(2) set default = 0;

    Darn thing it was working fine, or maybe I have been working
    for too many hours and my brain has stopped working

    Appreciate your correction.

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SitePoint Addict
    Join Date
    Oct 2006
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try:

    alter table articles add column test int(2) default '0';

    and get some rest....

    mikem

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you Mike for correction.

    One more related question:
    when I try to alter an existing column to state that it should have a default value, it is not working. That is I am issuing this:

    alter table articles modify column test default '0';
    or
    alter table articles modify column test set default '0';

    and in both case I get Error message.

    Regards,

    P.S., And Yes, I need to get some rest, will as soon as this project is done


    http://www.sitepoint.com/forums/show...0&goto=newpost

    Quote Originally Posted by mikemckinney View Post
    try:

    alter table articles add column test int(2) default '0';

    and get some rest....

    mikem

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    your syntax is slightly off. "modfy" is not correct. have you looked here yet?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    I checked this page and could not find the answer to my question.
    So if you dont mind kindly tell me what the correct syntax should be.

    Regards,

    Quote Originally Posted by longneck View Post
    your syntax is slightly off. "modfy" is not correct. have you looked here yet?

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SitePoint Addict
    Join Date
    Oct 2006
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try:

    alter table articles modify column test int(2) default '0';

    You need to include a complete column specification - not just add the default clause.

    mikem

  7. #7
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Just as soon as I posted this message, I figured out what I was doing right!
    I am surprised that one can write
    default '0'
    and
    default 0
    in case of an integer number value!

    Regards,

    Quote Originally Posted by mikemckinney View Post
    try:

    alter table articles modify column test int(2) default '0';

    You need to include a complete column specification - not just add the default clause.

    mikem

    Anoox search engine volunteer

    www.anoox.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i still cannot figure out why people put strings into numeric columns

    why? why, why, why????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Not sure if you are answering my question or you are wondering about same
    question I have!
    Which again is how can value of an integer column be both
    '1'
    and
    1

    Regards,

    Quote Originally Posted by r937 View Post
    i still cannot figure out why people put strings into numeric columns

    why? why, why, why????

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the value of an integer column can never be '1'

    which is why i wonder why some people assign values like that

    yes, mysql "silently" converts the strings to numbers (other database systems will give you a syntax error, quickly and with no beatin' aroun' da bush)

    but i see that as unnecessary overhead -- minuscule, to be sure, but overhead nevertheless

    i am still left wondering why some people do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the line you should be looking at in the mysql manual is this:
    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
    you keep using MODIFY. the correct keyword is ALTER.

    and seriously, STOP USING THE QUOTE BUTTON. including the entire previous post in your post in unnecessary and distracting.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •