Trigger default value of a column during UPDATE

问题:

I am wondering about this case with Oracle 12c;

1. Table X has column A, with default value 'default' and NOT NULL modifier
2. I insert a new row into Table X, and column A has value 'not-default'
3. I wish to update column A of the above row to the default value of the given column, namely 'default'

Is there a short way of doing this without knowing the default value? Can I do something like;

UPDATE X SET A = DEFAULT_VAL(A) WHERE ...

Trying to update to null obviously triggers a ORA-01407: cannot update ("schema"."X"."A") to NULL, I'd like to know if there is such a feature on Oracle.

回答1:

We can you default keyword in update and insert statement. eg

 update x set A = default where ...

This will be helpful for your case and also in another case like i don't want add column clause in insert statement like

insert into table values (val1, val2, default);

If we couldn't use default then we have to add column clause, same query will be like below

insert into table (col1, col2, col3) values (val1, val2, default);