SQL: Setting Value to Column with NULL values

Here is the structure of how to update a table column which has NULL values in it.

UPDATE TableName SET ColumnName = NewValue where ColumnName Is Null

Now lets apply it against a real scenario.

update Student set IsAdded = 0 where IsAdded Is Null

After executing this query all the values for the column IsAdded gets to 0 where it has NULL values.

Hope you have got the idea.

SQL: Set Default Value for a Column

Below is the structure for setting the default value for a column.  You will not face NULL values in the columns where this constraint gets added.

ALTER TABLE TableName
ADD CONSTRAINT
ADDED DEFAULT DefaultValue FOR ColumnName

Now lets apply it with a table so you will get more idea of how to work with it.


ALTER TABLE Student
ADD CONSTRAINT
ADDED DEFAULT 0 FOR IsAdded

Hope you have got the idea.

SQL: Remove Column from Table

Here is the format which can be used to remove one or more columns from an already existing table:

ALTER TABLE table-name
DROP COLUMN column-name

Now lets implement it with a table Student which have following fields:

ID,
First Name,
Last Name,
Middle Name,
Address

Lets say we want to remove Middle Name
So we will use the following query:

ALTER TABLE Student
DROP COLUMN Middle Name


Hope you have got the idea how to delete the columns from an existing table.

SQL: Add Multiple Columns in Table

Here  is the statement skeleton for adding new column in an existing table.

ALTER TABLE table_name
ADD column_name1 datatype
ADD column_name2 datatype
ADD column_name3 datatype
.................................................
.................................................
.................................................
ADD column_nameN datatype


Lets explain it using an example:

Lets suppose I have a table Student and I want to add new columns int it then I will write the query like this.

ALTER TABLE Student
ADD CourseName varchar(50)
ADD Address varchar(100)
ADD SemesterNo int
ADD PassingOutYear DateTime


So all these columns gets added to the table.