Do not fill new column as Null and yes with specific value in the script

Navigation
1

I have a specific table in the database, in it I keep information of classes that are created, swimming classes, futsal classes, bodybuilding 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, which will save the student's tolerance of entry time in the club concierge, for example if a swimming class is created, it will be registered in a tolerance time in minutes, for example 10 minutes, this means that the student can only arrive up to 10 minutes before class, if he arrives 20 minutes before, access is blocked. because the problem is, as the classes that already exist have not yet made this configuration, by default, I will leave to be this column class with 15 (in this 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 15 filled for example?

I'm using sqlserver2017

    
asked by anonymous 27.11.2018 / 18:45

3 answers

2

Add the column with a default value with the WITH VALUES statement so that existing rows receive this value, this will also give you a breather to tweak the system that has yet to be changed to add this information in the operations CRUD. 

   ALTER TABLE [TABELA] ADD [COLUNA] INT NOT NULL
   CONSTRAINT Tabela_Coluna 
   DEFAULT (15)
   WITH VALUES
    
27.11.2018 / 19:03
2

John, good afternoon! You have to enter the column as nullable , after that you do an update on the existing records for the 15 minute value you want.

Once this is done and you know 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 have helped

Update table set Column New = 15

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

    
27.11.2018 / 18:53
1

There are two ways:

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

2) Run a script: 

UPDATE {table} SET {column} = 15 WHERE {column} IS NULL
    
27.11.2018 / 18:53
Place event button to access page Apply the Blur effect to the floating box bottom