Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: a few easy noob questions about tables

    i have made a table. but i want to add more columns to it. which command do i use? UPDATE?


    what is joining tables? I need to make a table which contains the list of the states in US, and then somehow each state needs to link to its own unique table.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It would help us do your homework if we actually had a copy of it. Do you have a URL for a web site where we can view it online, or a copy that you can post for us to download?

    -PatP

  3. #3
    Join Date
    Dec 2003
    Posts
    39
    if u wanna add column, the easiest way is just using table design in Enterprise manager. if wanna use script, see the sql server books online for
    alter table :
    ALTER TABLE <table name> ADD <column definition>
    column definition would be the column name, type, default value, constraint, ...
    Link Link

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Ouch Pat that was mean!

    (I wait to see if it was justified though.)

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Ryker
    Ouch Pat that was mean!
    (I wait to see if it was justified though.)
    Not at all, it is better to motivate a noob to do some research on his own and actually read the manuals.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Dec 2005
    Posts
    3
    ya i just finished reading some tutorials online.

    so i have several tables...bare with me


    -North_America

    -Nord_Province (holds names of both candian provinces and american states)

    -British_Columbia to Wyoming (each table named after the state, and holds names of stadiums in that state/province...so about 70 tables )

    This what i've done:

    alter table North_America
    add column country_id not null primary key auto_increment;


    and i saw when i select*from North America

    the display shows that Canada with country_id "1" and United States with country_id "2".

    then i made a second table which includes both all the province of Canada and states of America. nord_province
    (canadian province first and american states following alphabetically after the last canadian province. ex British Columbia.....Newfoundland, Arizona, Alabama...etc)

    Now I have to indicate id "1" to all Candian province and id "2" to all American states. i didn't add auto_increment (since i only need two numbers 1 & 2), and it won't let me do this. I made another column state_id which is a primary key not null auto_increment.

    here's a table of what im trying to achieve. state_id i was easily able to do. the problem is creating a non auto incrementating id.

    |name VARCHAR(40) | id INT | state_id |

    |British Columbia | 1 | 1 |
    |Alberta | 1 | 2 |
    |Sasktchewan | 1 | 3 |
    |..... | 1 | 4 |
    |... | 1 | 5 |
    |... | 1 | 6 |
    |Arizona | 2 | 7 |
    |Alabama | 2 | 8 |

    something like this.... but do i still use PRIMARY KEY for this? because my id is not unique for all individual province and states. i read on this....but still confused...the books weren't too clear.

    Ok, finally i have basically another table holding extensive data of all the stadium names for each state and province.

    so i would probably have about 70 different tables for each province or state with state_id and stadium names in that region.

    so for british columbia...i would have something like this.

    |stadium_name VARCHAR(40) | state_id |

    |GM place | 1 |
    |XXX stadium | 1 |
    |bal bal stadium | 1 |
    |XYXY stadium | 1 |
    |... | 1 |
    |... | 1 |

    and for alberta....i would have similiar columns but all state_id have "2" and the next province with state_id with "3" for all.


    whew....well this is the best i can put it as....help would be greatly appreciated. basically im looking for a way to make a column for key in without auto incrementation.... or any other way is good too.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by downwiz2
    i have made a table. but i want to add more columns to it. which command do i use? UPDATE?
    No; with UPDATE, INSERT and DELETE you change the contents of the table, not the structure.
    If your database system allows it, you may add columns with the "ALTER TABLE" SQL statement:
    Code:
    ALTER TYPE mytable ADD column_name DATA_TYPE
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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