How do I change the default value for some rows in SQL?


Fistly, I need to make all rows in a column called Mail_flag have a default of 'Y'. I did this already with the following line of code:

ALTER TABLE criminals
     MODIFY (Mail_flag DEFAULT 'Y');

Easy enough. But now, I need to make all rows that do NOT have a street address (where the column name is "street") have a default value of 'N'. I assumed the code would like something like the following:

ALTER TABLE criminals
     MODIFY (Mail_flag DEFAULT 'N')
     WHERE street = null;

However, I received an error message which said "Invalid Alter Table Option." I then tried an Update command, which also failed. Basically, I need two default values. One that is a 'Y', and one that is an 'N', only when a certain condition applies (the condition being that the street value is null). Please help! I've been struggling with this for 2 hours.

WHERE street = null;

That is so wrong. You don't understand what NULL value means. Though your alter statement won't be executing even if you fix it, but you shouldn't do it as it is wrong. The correct way to filter the rows is where column IS NULL

For your requirement, do a one time update :

update criminals
   Set mail_flag = 'N'
where street is null;

Then, implement a trigger to do the same update whenever it finds the old value as null.

I am having another thought now. Why do you want to update the column at all? So that, you could use the values in an operation or to display, isn't it? So, you can always use functions like DECODE and NVL to return the value N whenever column is null.