| Subject | RE: [IBDI] Defining a default value to an existing column |
|---|---|
| Author | David Schnepper |
| Post date | 2000-06-08T22:25:21Z |
update
rdb$fields
set rdb$sql_default =
'100000000'
where
rdb$field_name = (select rdb$global_field from
rdb$relation_fields r
where r.rdb$relation_name = 'ORDERS'
and r.rdb$field_name = 'OR_SITUATION')
I'm
pretty sure I have some of the column names wrong above, and you may also have
to alter
a blr
field (that consists of the default value in a blr expression) -- so twiddle
with it some.
I'd
also try
ALTER
TABLE ORDERS ALTER COLUMN OR_SITUATION DEFAULT '10000000';
(eg:
don't try to specify a new type as well as a new default)
Dave
-----Original Message-----At 02:16 PM 08-06-00 +0000, you wrote:
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, June 08, 2000 10:00 AM
To: IBDI@egroups.com
Subject: Re: [IBDI] Defining a default value to an existing column
>What is the command syntax for defining a default value to an
>existing column on a Interbase 6.0 table on WISQL ?
>I tried something like:
>
>ALTER TABLE ORDERS ALTER COLUMN OR_SITUATION TYPE VARCHAR(8)
>DEFAULT '10000000';
>
>but it always returns a SQL error.
>
>Thank you in advance,
>
>Paulo
You can't alter a column.
This is what you have to do:
1. Add a new column of the same data type
ALTER TABLE ORDERS ADD NEWCOLUMN VARCHAR(8);
2. Park the data from the old column in the new column:
UPDATE ORDERS SET NEWCOLUMN=OR_SITUATION;
3. Replace nulls with the default value
UPDATE ORDERS SET NEWCOLUMN='1000000' WHERE NEWCOLUMN IS NULL;
4. Drop the old column
ALTER TABLE ORDERS DROP OR_SITUATION;
5. Recreate the old column
ALTER TABLE ORDERS ADD OR_SITUATION VARCHAR(8) NOT NULL DEFAULT '10000000';
6. Move the data back
UPDATE ORDERS SET OR_SITUATION=NEWCOLUMN;
7. Drop the temporary column
ALTER TABLE ORDERS DROP NEWCOLUMN;
If you don't need the Not Null constraint, you can cut out a couple of
steps here..
HB
http://www.interbase2000.org
___________________________________________________
"Ask not what your free, open-source database can do for you,
but what you can do for your free, open-source database."
(J.F.K.)
Community email addresses:
Post message: IBDI@onelist.com
Subscribe: IBDI-subscribe@onelist.com
Unsubscribe: IBDI-unsubscribe@onelist.com
List owner: IBDI-owner@onelist.com
Shortcut URL to this page:
http://www.onelist.com/community/IBDI