Search⌘ K
AI Features
Home
Courses
A Practical Guide to SQL and Database Fundamentals

Data Manipulation Language (DML)

Explore the core Data Manipulation Language commands in SQL Server. Learn how to insert new data, update existing records carefully, and delete data using conditions to avoid errors. This lesson helps you gain practical skills for manipulating table data safely and effectively.

We'll cover the following...

Data Manipulation Language (DML) is precisely what it sounds like; it manipulates the data. These statements are part of DML and allow us to modify existing data, create new data, or even delete it.

Note: DML is not specific to objects like modifying a table’s structure or a database’s settings but works with the data in the tables.

The three data manipulation statements are:

  • INSERT
  • UPDATE
  • DELETE

The following sections discuss each of the above DML statements one by one.

The INSERT statement

The INSERT statement allows us to insert data into the tables of a particular database. We use the following syntax to insert three rows into the table in lines 5–7 below:

MySQL
INSERT INTO TableName
(ColumnName1,ColumnName2) -- Mention the columns to insert values in.
VALUES
('Value1',1), -- Inserting "Value1 " in Column_Name1 and the number 1 in Column_Name2.
('Value2',2),
('Value3',3)

The UPDATE statement

The UPDATE statement allows us to update existing data. For instance, we may change an existing piece of data in a table to another value.

But we should be careful when updating values in a table as we could set all rows within one column to the same value if we don’t specify a condition in the WHERE clause.

Note: We can always check which values we’re updating by swapping out UPDATE with SELECT and commenting out the line that begins with SET, so long as we’re using the WHERE clause. When updating one value, it’s best to use the primary key as a condition in the WHERE clause.

MySQL
UPDATE TableName
SET 
     ColumnName1 = 'New Value 1' -- Specify the new value here.
     ColumnName2 = 'New Value 2'
WHERE 
     ColumnName1 = 'Old Value' -- using the WHERE clause as a condition.

We can update a value or set of values in one column of a table using the following syntax:

MySQL
UPDATE TableName
SET ColumnName1 = 'Value'

The DELETE statement

The DELETE statement deletes data from a specified object, like a table. It’s best to use to be careful when running the DELETE statement.

If we don’t use the WHERE clause in the query, we’ll delete all of the data within the table. So, it’s always best to use a WHERE clause in a DELETE statement.

Note: Much like the UPDATE statement, we can also swap out DELETE with SELECT in the statement to see what data it would affect, so long as we’re using the WHERE clause in the query. Like the UPDATE statement, when deleting one value, it’s best to use the primary key as a condition in the WHERE clause.

We use the following syntax to delete a certain value from a table using the WHERE clause:

MySQL
DELETE FROM TableName
WHERE ColumnName1 = 'Some_Value'

To delete all the values in the table, use:

MySQL
DELETE FROM TableName

Let's see how to execute the above-mentioned DML commands by clicking the “Run” button below:

-- We have used additional SELECT statements to show the effect of the queries
CREATE DATABASE Educative -- Creating database Educative
GO

CREATE SCHEMA Ed        -- Creating schema Ed
GO

CREATE TABLE Ed.Department -- Creating Table Department
(
     DepId INT PRIMARY KEY,
     DepName VARCHAR(30)
     
)
GO

SELECT * FROM Ed.Department -- Select statement to see that the table has been created
GO

ALTER DATABASE Educative MODIFY NAME = EducativeIO -- changing database name
GO
ALTER TABLE Ed.Department -- Altering the table by adding a new column
ADD DepFloor INT
GO

SELECT * FROM Ed.Department -- Select statement to see that the new column has been added
GO


SELECT "--INSERT--"
-- Insert values in the table
INSERT INTO Ed.Department
(DepId, DepName, DepFloor) -- Mention the columns to insert values in.

VALUES
(1, 'Content', 1), 
(2, 'Development', 2),
(3, 'Human Resource', 3)
GO
SELECT * FROM Ed.Department -- Select statement to see that the data has been inserted.
GO

SELECT "--UPDATE--"
-- Update the Department
UPDATE Ed.Department

SET 
     DepName = 'Dev', -- Specify the new value here.
     DepFloor = '4'
WHERE 
     DepId = '1'
GO 

SELECT * FROM Ed.Department -- Select statement to see that the table has been updated.
GO
SELECT "--DELETE --"
-- Delete a value from Department
DELETE FROM Ed.Department
WHERE DepFloor = 3
GO
SELECT * FROM Ed.Department -- Select statement to see that the records have been deleted.
GO
Practice the syntax