How to change default null value from a table fiel
Hi to all, I have a database table that has a column with a NULL default value, but now I need to change this to a NOT NULL default 999 value. If I execute this expression "ALTER TABLE my_table ADD (existing_field SMALLINT DEFAULT 999 NOT NULL)" I get a 328 error: Column (existing_field) already exists in table or type. What am I doing wrong? How to add a non null default value to an existing field? Any help will be appreciated. Nando.
with "Add" you are trying to add a column. What you want is "Modify"
alter table foo modify(my_column not null default 999)
Os something like that.
j.
On Feb 23, 2012, at 12:14 PM, HERNANDO DUQUE wrote:
> Hi to all,=20
>=20
> I have a database table that has a column with a NULL default value, =
but now I=20
> need to change this to a NOT NULL default 999 value.=20
>=20
> If I execute this expression "ALTER TABLE my_table ADD (existing_field=20=
> SMALLINT DEFAULT 999 NOT NULL)" I get a 328 error: Column =
(existing_field)=20
> already exists in table or type.=20
>=20
> What am I doing wrong?=20
> How to add a non null default value to an existing field?=20
> Any help will be appreciated.=20
>=20
> Nando.=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
You don´t need to ADD but to MODIFY the field
ALTER TABLE my_table MODIFY (existing_field SMALLINT DEFAULT 999 NOT NULL)
Is the claus you are looking for.
You also need to update the field from NULL to the new default of 999 with an
UPDATE my_table.....
Regards,
Joerg Volz
-----------------------------------------------------------------------------
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of HERNANDO
DUQUE
Sent: Thursday, February 23, 2012 6:16 PM
To: ids@iiug.org
Subject: How to change default null value from a table fiel [26343]
Hi to all,
I have a database table that has a column with a NULL default value, but now I
need to change this to a NOT NULL default 999 value.
If I execute this expression "ALTER TABLE my_table ADD (existing_field
SMALLINT DEFAULT 999 NOT NULL)" I get a 328 error: Column (existing_field)
already exists in table or type.
What am I doing wrong?
How to add a non null default value to an existing field?
Any help will be appreciated.
Nando.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
IT Handel und Beratung Jörg Volz
Bernhard-Früh-Str. 7
77855 Achern
GERMANY
Tel: +49 (0)7841-681651
Fax: +49 (0)7841-681654
Mobil: +49 (0)170-2989757
VAT-ID: DE201383541
http://www.it-volz.de
Alter table table modify (existingcolumn smallint default 999 not null);
Art
On Feb 23, 2012 12:15 PM, "HERNANDO DUQUE" <hduquec@hotmail.com> wrote:
> Hi to all,
>
> I have a database table that has a column with a NULL default value, but
> now I
> need to change this to a NOT NULL default 999 value.
>
> If I execute this expression "ALTER TABLE my_table ADD (existing_field
> SMALLINT DEFAULT 999 NOT NULL)" I get a 328 error: Column (existing_field)
> already exists in table or type.
>
> What am I doing wrong?
> How to add a non null default value to an existing field?
> Any help will be appreciated.
>
> Nando.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba21224be04d2904b9a51294
Opsss... Now I realize mi mistake. Thank you all for posting. Nando.