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.

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

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, ...

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.

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.

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: