Introduction to INSERT, UPDATE and DELETE SQL commands

Data Manipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed. It means changes made by DML command are not permanent to database, it can be rolled back.

Talking about the Insert command, whenever we post a Tweet on Twitter, the text is stored in some table, and as we post a new tweet, a new record gets inserted in that table.

INSERT command

Insert command is used to insert data into a table. Following is its general syntax,

INSERT INTO table_name VALUES(data1, data2, ...)

Lets see an example,

Consider a table student with the following fields.

INSERT INTO student VALUES(101, 'Adam', 15);

The above command will insert a new record into student table.

Insert value into only specific columns

We can use the INSERT command to insert values for only some specific columns of a row. We can specify the column names along with the values to be inserted like this,

INSERT INTO student(id, name) values(102, 'Alex');

The above SQL query will only insert id and name values in the newly inserted record.

Insert NULL value to a column

Both the statements below will insert NULL value into age column of the student table.

INSERT INTO student(id, name) values(102, 'Alex');

Or,

INSERT INTO Student VALUES(102,'Alex', null);

The above command will insert only two column values and the other column is set to null.

Insert Default value to a column

INSERT INTO Student VALUES(103,'Chris', default)

Suppose the column age in our tabel has a default value of 14.

Also, if you run the below query, it will insert default value into the age column, whatever the default value may be.

INSERT INTO Student VALUES(103,'Chris')

Let's take an example of a real-world problem. These days, Facebook provides an option for Editing your status update, how do you think it works? Yes, using the Update SQL command.

Let's learn about the syntax and usage of the UPDATE command.

UPDATE command

UPDATE command is used to update any record of data in a table. Following is its general syntax,

UPDATE table_name SET column_name = new_value WHERE some_condition;

WHERE is used to add a condition to any SQL query, we will soon study about it in detail.

Lets take a sample table student,

UPDATE student SET age=18 WHERE student_id=102;

In the above statement, if we do not use the WHERE clause, then our update query will update age for all the columns of the table to 18.

Updating Multiple Columns

We can also update values of multiple columns using a single UPDATE statement.

UPDATE student SET name='Abhi', age=17 where s_id=103; 

The above command will update two columns of the record which has s_id 103.

UPDATE Command: Incrementing Integer Value

When we have to update any integer value in a table, then we can fetch and update the value in the table in a single statement.

For example, if we have to update the age column of student table every year for every student, then we can simply run the following UPDATE statement to perform the following operation:

UPDATE student SET age = age+1; 

As you can see, we have used age = age + 1 to increment the value of age by 1.

NOTE: This style only works for integer values.

Let's study about the syntax and the usage of the Delete command.

DELETE command

DELETE command is used to delete data from a table.

Following is its general syntax,

DELETE FROM table_name;

Let's take a sample table student:

Delete all Records from a Table

DELETE FROM student;

The above command will delete all the records from the table student.

Delete a particular Record from a Table

In our student table if we want to delete a single record, we can use the WHERE clause to provide a condition in our DELETE statement.

DELETE FROM student WHERE s_id=103;

The above command will delete the record where s_id is 103 from the table student.

Isn't DELETE same as TRUNCATE

TRUNCATE command is different from DELETE command. The delete command will delete all the rows from a table whereas truncate command not only deletes all the records stored in the table, but it also re-initializes the table(like a newly created table).

For eg: If you have a table with 10 rows and an auto_increment primary key, and if you use DELETE command to delete all the rows, it will delete all the rows, but will not re-initialize the primary key, hence if you will insert any row after using the DELETE command, the auto_increment primary key will start from 11. But in case of TRUNCATE command, primary key is re-initialized, and it will again start from 1.