Re: Beginner - Update table

"Mark Muller" <mmu_at_beer.com> wrote in message news:<aspm6a$mvj$1_at_news.hccnet.nl>...
> How to write a sql script that whill alter an existing table to a new
> definition
>
> I have to modify tables, i know what the columns should be, but not what
> they were (depending on last change at customer site),
> for example the following table where MOB is a new column. When i try this
> oracle (9i) is complaining the column ID already is NOT NULL and does not
> add the MOB column.
>
> ALTER TABLE vaccin.HUISARTS MODIFY (
> ID CHAR(10) not null,
> NAME CHAR(30) not null,
> TEL CHAR(15) null,
> FAX CHAR(15) null,
> MOB CHAR(15) NULL
> );

Mark, you ADD new columns and MODIFY existing columns. IF MOB doesn't exist your are using the wrong command. If it does exist then in a case where you do not know what the table looks like at the customer site it might be wise to perform each column modification in a separate alter command because if any of the column statements fail the whole statement fails as written.

You might also want to consider changing your char columns to varchar2, but that is besides the point of your post.

IMHO -- Mark D Powell -- Received on Fri Dec 06 2002 - 08:25:31 CST