SQL DML Commands

Introduction of SQL DML Commands

Data Manipulation Language (DML) commands in SQL deals with manipulation of data records stored within the database tables. It does not deal with changes to database objects and its structure. The commonly known DML commands are INSERT, UPDATE and DELETE. Liberally speaking, we can consider even SELECT statement as a part of DML commands. Albeit, it strictly forms part of the Data Query Language (DQL) command.

we will be learning about all the above mentioned DML commands in great detail in the subsequent sections. But let us first have a look at this summary table for a brief overview on each of them.

Command	Description
SELECT	Used to query or fetch selected fields or columns from a database table
INSERT	Used to insert new data records or rows in the database table
UPDATE	Used to set the value of a field or column for a particular record to a new value
DELETE	Used to remove one or more rows from the database table

Commands of DML

Now let us try to understand each of the above mentioned DML commands in detail one by one.

1. SELECT

SELECT command or statement in SQL is used to fetch data records from the database table and present it in the form of a result set. It is usually considered as a DQL command but it can also be considered as DML.

The basic syntax for writing a SELECT query in SQL is as follows :

SELECT column_name1, column_name2, …
FROM table_name
WHERE condition_ expression;

The parameters used in the above syntax are as follows :

• column_name1, column_name2, … : Specify the column_names which have to be fetched or selected for the final result set.
• table_name: Specify the name of the database table from which these results have to be fetched.
• condition_expression: Specify the condition expression for filtering records for the final result set.

Here are a few examples to illustrate the use of SELECT command.

SELECT customer_id,
sale_date,
order_id,
store_state
FROM customers;

The query returns the following output.

In this example, we have fetched fields such as customer_id, sale_date, order_id and store_state from customers table. Next, suppose if we want to fetch all the records from the customers table. This can be achieved by a simple query as shown below.

SELECT * FROM customers;

The query returns the following output.

2. INSERT

INSERT commands in SQL are used to insert data records or rows in a database table. In an INSERT statement, we specify both the column_names for which the entry has to be made along with the data value that has to be inserted.

The basic syntax for writing INSERT statements in SQL is as follows :

INSERT INTO table_name (column_name_1, column_name_2, column_name_3, ...)
VALUES (value1, value2, value3, ...)

By VALUES, we mean the value of the corresponding columns.

Here are a few examples to further illustrate the INSERT statement.

INSERT INTO public.customers(
customer_id, sale_date, sale_amount, salesperson, store_state, order_id)
VALUES (1005,'2019-12-12',4200,'R K Rakesh','MH','1007');

Here we have tried to insert a new row in the Customers table using the INSERT command. The query accepts two sets of arguments, namely field names or column names and their corresponding values.

Suppose if we have to insert values into all the fields of the database table, then we need not specify the column names, unlike the previous query. Follow the following query for further illustration.

INSERT INTO customers
VALUES ('1006','2020-03-04',3200,'DL', '1008');

In this example, we have successfully inserted all the values without having to specify the fieldnames.

3. UPDATE

UPDATE command or statement is used to modify the value of an existing column in a database table.

The syntax for writing an UPDATE statement is as follows :

UPDATE table_name
SET column_name_1 = value1, column_name_2 = value2, ...
WHERE condition;

Having learnt the syntax, let us now try an example based on the UPDATE statement in SQL.

UPDATE customers
SET store_state = 'DL'
WHERE store_state = 'NY';

In this example, we have modified the value of store_state for a record where store_state was ‘NY’ and set it to a new value ‘DL’.

4. DELETE

DELETE statement in SQL is used to remove one or more rows from the database table. It does not delete the data records permanently. We can always perform a rollback operation to undo a DELETE command. With DELETE statements we can use the WHERE clause for filtering specific rows.

The syntax for writing an DELETE statement is as follows :

DELETE FROM table_name WHERE condition;

Having learnt the syntax, we are all set to try an example based on the DELETE command in SQL.

DELETE FROM customers
WHERE store_state = 'MH'
AND customer_id = '1001';

In this example, we have removed a row from the customer’s table where store_state was ‘MH’ and customer_id was ‘1001’.

Conclusion

DML commands are used to modify or manipulate data records present in the database tables. Some of the basic DML operations are data insert (INSERT), data updation (UPDATE), data removal (DELETE) and data querying (SELECT).

Recommend ed Articles

This is a guide to SQL DML Commands. Here we also discuss the syntax and parameters along with DML commands in detail with examples. You may also have a look at the following articles to learn more –

1. SQL WITH AS Statement
2. MySQL List User
3. PostgreSQL Extensions
4. PostgreSQL ORDER BY DESC