Alter table to modify default value of column

Question

I have a requirement where we need to modify a column's default value in database table. The table is already an existing table in database and currently the default value of the column is NULL. Now if add a new default value to this column, If I am correct it updates all the existing NULLs of the column to new DEfault value. Is there a way to not to do this but still set a new default value on column. I mean I do not want the existing NULLs to be updated and want them to remain as NULLs.

Any help on this is appreciated. Thanks

User · Answer

ALTER TABLE  TABLE NAME  ALTER COLUMN  COLUMN NAME  SET DEFAULT   DEFAULT VALUES     example    ALTER TABLE RESULT ALTER COLUMN STATUS SET DEFAULT  FAIL

User · Answer

Your belief about what will happen is not correct   Setting a default value for a column will not affect the existing data in the table   I create a table with a column col2 that has no default value  SQL gt  create table foo    2    col1 number primary key    3    col2 varchar2 10    4      Table created   SQL gt  insert into foo  col1   values  1    1 row created   SQL gt  insert into foo  col1   values  2    1 row created   SQL gt  insert into foo  col1   values  3    1 row created   SQL gt  select   from foo         COL1 COL2 ---------- ----------          1          2          3   If I then alter the table to set a default value  nothing about the existing rows will change  SQL gt  alter table foo   2    modify  col2 varchar2 10  default  foo      Table altered   SQL gt  select   from foo         COL1 COL2 ---------- ----------          1          2          3  SQL gt  insert into foo  col1   values  4    1 row created   SQL gt  select   from foo         COL1 COL2 ---------- ----------          1          2          3          4 foo   Even if I subsequently change the default again  there will still be no change to the existing rows  SQL gt  alter table foo   2    modify  col2 varchar2 10  default  bar      Table altered   SQL gt  select   from foo         COL1 COL2 ---------- ----------          1          2          3          4 foo  SQL gt  insert into foo  col1   values  5    1 row created   SQL gt  select   from foo         COL1 COL2 ---------- ----------          1          2          3          4 foo          5 bar

User · Answer

Following Justin s example  the command below works in Postgres   alter table foo alter column col2 set default  bar

User · Answer

ALTER TABLE  lt table name gt  MODIFY  lt column name gt  DEFAULT  lt defult value gt   EX  ALTER TABLE AAA MODIFY ID DEFAULT AAA SEQUENCE nextval   Tested on Oracle Database 12c Enterprise Edition Release 12 2 0 1 0

User · Answer

ALTER TABLE  table name  MODIFY  column name  DEFAULT  value    worked in Oracle e g  ALTER TABLE MY TABLE MODIFY MY COLUMN DEFAULT 1

User · Answer

For Sql Azure the following query works    ALTER TABLE  TableName  ADD  DEFAULT  DefaultValue  FOR ColumnName GO

[sql] Alter table to modify default value of column

The answer is

Examples related to sql

Examples related to oracle

Examples related to alter-table

Tags