All You Need To Know About Data Manipulation Language Statements (DML)

DML, Data Manipulation Language, commands in Structured Query Language alter the data in the SQL database. Accessing, storing, modifying, updating, and deleting existing database records is made simple by DML commands.

Data in existing tables can be accessed and altered using statements written in the data manipulation language (DML). Oracle Database views a transaction as a collection of one or more SQL statements: Either all of the statements are carried out or none of them are.

We will learn more about DML statements in the paragraphs that follow. Or else you can also enroll in an online SQL server training course and learn about DML command statements in detail. 

What are Data Manipulation Language ( DML) Statements

The SQL data manipulation language (DML) can access and modify data from databases. This blog will explain how to use the SELECT, INSERT, UPDATE, and DELETE SQL DML command statements, which are defined below.

• INSERT  – To insert data into a table
• SELECT  – To query data in the database
• UPDATE – To update data in a table
• DELETE – To delete data from a table 
• MERGE -- To synchronize data in two tables 

In the SQL DML statement:

• Each clause in a statement should begin on a new line.
• The beginning of each clause should align with the beginning of other clauses.
• If a clause has several parts, they should appear on separate lines and be indented under the start of the clause to show the relationship.
• Upper case letters are used to represent reserved words.
• Lowercase letters are used to represent user-defined words.

Different Types of Data Manipulation Language (DML) Statements

SQL Server DML – INSERT Statement

When adding new columns to a table, the explanation for the SQL Waiter Supplement is used. Before reading this statement, please consider the following points:

• Value by Default: When a new table is created, a default value can be set for a particular column (DDL). If nothing else is specified, this is the value you want to enter into the field of that column. For example: "2014-23-01" is the default employment date, and the default salary is $5,700.
• The INSERT demonstrations that follow are based on the table below. The emp_sal segment has a default worth of 5700, and there is just a single line in the table right now.

Basic Syntax
INSERT INTO table_name
VALUES (value, value, value  …)
Inserting a new row into the table
INSERT INTO emp
VALUES (2, 'David', 3200, ’2014/03/20’)

The following table reflects the data in the Emps table after the INSERT statement has been completed:

Important Points

• It is necessary to list the values in the same order as the columns in the table. The first value must be linked to the employee number column in the above scenario; the second value must be linked to the employee name column, and so on.
• The type of the value must correspond to the type of the columns in the table. The emp_hiredate column, for example, cannot accept the value 37.
• The number of values must match the number in the table. You can only enter as many or as few values as the table allows—four.
•  When entering a NULL value into one of the columns' fields, use the NULL keyword.

SQL Server Insert Into Select

This method allows copying data items from another table into the requested target table.

INSERT INTO  target_table_name (column_name, column_name, column_name ..)
SELECT  …
FROM source_table_name
WHERE   …

For Example:

INSERT INTO emp (emp_id , emp_name , emp_salary)
SELECT employee_id , last_name , salary
FROM   employees
WHERE  department_id = 50

• You can use the INSERT INTO SELECT statement either with or without the column list.
• It is not mandatory to include a WHERE statement in the query; however, including such a statement is advisable if you would like to avoid copying the entire table.

SQL Server DML – Update Statement

The SQL Server UPDATE statement is used to modify existing rows.

UPDATE table_name
SET column=value, column=value ..
WHERE condition
Updating the salary of employee no. 100:
UPDATE employees
SET salary = 5000
WHERE employee_id = 100

It is possible to update several fields simultaneously, for example, update salary, last name, and first name of employee no. 100:

UPDATE employees
SET salary = 5000 , last_name = 'Doe' ,
First_name = 'John'
WHERE employee_id = 100

A subquery can be nested in the SQL Server UPDATE statement. For example, updating the salary of the employees in department 60 so it would match the average salary of the employees in department 50:

UPDATE employees
SET salary = (SELECT AVG(salary)
FROM employees
WHERE department_id = 50)
WHERE department_id = 60

It is possible to update to a NULL or DEFAULT value.

UPDATE employees
SET salary = DEFAULT
WHERE last_name = 'King'  
UPDATE employees
SET salary = NULL
WHERE department_id = 90

Executing the SQL Server UPDATE statement without using the SQL Server WHERE clause results in updating all fields in a column.

SQL Server DML – DELETE Statement

The SQL Server DELETE statement removes existing rows from a table.

DELETE FROM table_name
WHERE condition
Deleting the row that contains the data of employee no. 103:
DELETE FROM employees
WHERE employee_id = 103
A subquery can be nested in the DELETE statement.
DELETE FROM employees
WHERE department_id = (SELECT department_id FROM departments
WHERE department_name = 'Sales')

• Executing the SQL Server DELETE statement without using the SQL Server WHERE clause results in the deletion of all of the rows in the table.
• Deleting only a single field by using the SQL Server DELETE statement is impossible. The SQL Server DELETE statement deletes rows.
• You can write a SQL Server DELETE statement either with or without the SQL Server FROM keyword.

DELETE FROM employees
-- or
DELETE employees

SQL Server DML – MERGE Statement

The SQL Server MERGE statement synchronizes the data in two tables based on differences. The UPDATE operation is carried out if the same row exists in both tables, such as a customer id row, but each row has different values (each table holds a different phone number for that customer). Supplement activity will be carried out if only one table with the column exists. If you wish to pursue your career in SQL and wonder how much they get paid, check the SQL developer salary guide and the top companies hiring SQL developers around the world. 

The following examples demonstrate the merge concept. The Clients table's information is converged with the Reinforcements table's information toward the finish of every month. The only MERGE activity performed at the beginning of the month was to transfer (INSERT) all rows into the Backup table in their current state (assuming that this was the first time the Backup table was populated).

The accompanying information things are converged with the Reinforcement table toward the month's end because of changes made to the Client's table:

• An UPDATE operation serves customers who are already in the backup table; For instance, the size of George's Internet Package will be increased to 5.0 MB from 2.5 MB.
• An INSERT operation for new customers is used to add Roy, a new customer, to the backup table.

Basic syntax
MERGE INTO destination_table alias
USING source_table alias
ON condition
WHEN MATCHED THEN
UPDATE SET
destination_table_alias.column = source_table_alias.column,
destination_table_alias.column = source_table_alias.column
…
WHEN NOT MATCHED THEN
INSERT VALUES  (source_table_alias.column, source_table_alias.column ..

For Example

MERGE INTO  customers_backup backup
USING   customers cust
ON   (backup.cust_id = cust.cust_id)
WHEN MATCHED THEN
UPDATE SET
backup.cust_name = cust.cust_name,
backup.cust_surfing_package = cust.cust_surfing_package
WHEN NOT MATCHED THEN
INSERT VALUES(cust.cust_id , cust.cust_name , cust.cust_surfing_package)

Importance of Data Manipulation Language (DML)

DML statements can be used to alter the data stored in a database. Customers can choose what information is required. Various database manufacturers offer DML in a wide range of flavors and features. It makes system interaction easier to use.

Advantages of DML Statements

DML statements offer the following advantages:

• You can modify database data with DML statements.
• Users can specify the required data.
• There is a wide variety of DML flavors and capabilities available to database vendors.
• It makes system interaction easier to use.

Conclusion

We learned about SQL Server's DML statements in this article. We gained knowledge of its definition. Additionally, we gained knowledge of the various types of DML statements, their applications, and their benefits and drawbacks. I hope this article is a starting point for SQL Server users interested in learning more about DML statements. Let's dive into DML commands and learn more about their importance in SQL and key takeaways. You should check out the SQL career path guide to help you explore all your career options. 

SQL Training For Administrators & Developers

• No cost for a Demo Class
• Industry Expert as your Trainer
• Available as per your schedule
• Customer Support Available

Enroll for Demo Class