No Fill new column as Null, yes with specified value in script

Asked

Viewed 283 times

1

I have a specific table in the database, in it I keep information of classes that are created, swimming classes, futsal classes, weight training classes and so on, it is already in use and with several classes already registered, but now I need the following functionality in the system, I needed to add a new column , that will keep the time of tolerance of the student in the lobby of the club, for example if a swimming class is created, will be registered in it a tolerance time in minutes, for example 10 minutes, it means that the student can only arrive up to 10 minutes before class, if he arrives 20 minutes before, access is blocked. well the problem is, as the classes that already exist have not yet made this setting, by default I will leave to be classes this column with the value of 15 (in case 15 minutes), the question is, how do I make a script that instead of leaving the information in the classes that already exist as "null" leave filled 15 for example ?

am using sqlserver2017

c# sql sql-server script sql-server-express

3 answers

2

John, good afternoon to you! You have to insert the column as nullable, after that you update the existing records to the value of 15 minutes you want.

Once this is done and knowing that the field is mandatory, you will be able to leave the non-nullable and require system completion, otherwise you will have an Exception.

I hope I’ve helped

Update table set Colunanova = 15

****You can put the default value as the friend below said too, will depend on the necessity of your business rule. My update does not have Where because I understand that you want to do this process once and that there are no new records already in the table (where they are already filling the value of minutes), if there are these records, please consider the clause Where colunanova is null

2


Add the column with a default value with the instruction WITH VALUES for existing lines to receive this value, it will also give you a breather to adjust the system that still needs to be changed to add this information in CRUD operations.

   ALTER TABLE [TABELA] ADD [COLUNA] INT NOT NULL
   CONSTRAINT Tabela_Coluna 
   DEFAULT (15)
   WITH VALUES

  • Thank you Leandro

  • @Joãoávila if the solution presented solved your problem you must accept it as an answer. It would also be interesting to edit your question by adding the structure of your table, name and column type you are adding. And for the next questions, worry about always presenting a [MCVE]

1

There are two ways:

1) In Sql Management Studio 2017, right-click the table and Design. Select the column you want to set a Default value. In Column Properties, in the General tab, in Association or Default Value set to 15. Save the table.

2) Run a script:

UPDATE {table} SET {column} = 15 WHERE {column} IS NULL

Browser other questions tagged c# sql sql-server script sql-server-express

You are not signed in. Login or sign up in order to post.