Lesson 1

In the previous module, you focused on SELECT statements and learned how to retrieve data from one or more tables. In this module, you will build on that foundation by learning how to change data in your database using the three core Data Manipulation Language (DML) statements in SQL Server: INSERT, UPDATE, and DELETE.

Learning Objectives

After completing this module, you will be able to:

1. Use literal values in your INSERT, UPDATE, and DELETE queries.
2. Use INSERT and UPDATE queries to modify a table using values from another table.
3. Write INSERT, UPDATE, and DELETE statements using subqueries.
4. Apply basic performance and safety practices when writing DML statements in SQL Server.

1. Using Literal Values in INSERT, UPDATE, and DELETE

The most direct form of DML uses literal values in the statement. This is useful for simple changes, test data, and one-off data corrections.

INSERT with Literal Values

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (101, N'John', N'Doe', 60000);

UPDATE with Literal Values

UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;

DELETE with Literal Values

DELETE FROM Employees
WHERE EmployeeID = 101;

In application code, avoid building these statements through string concatenation. Use parameterized queries instead, whatever your client language's database library provides for binding parameters, to prevent SQL injection and to let SQL Server reuse execution plans rather than compiling a new one for every slightly different literal value.

2. Using Values From Another Table

In real systems, you often move or transform data between tables. SQL Server lets you populate or update a table directly from another table using INSERT, UPDATE, and JOIN or SELECT together.

INSERT from Another Table

INSERT INTO HighEarners (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM   Employees
WHERE  Salary > 100000;

UPDATE from Another Table

UPDATE  E
SET     E.Salary = B.NewSalary
FROM    Employees   AS E
JOIN    BonusTable  AS B
        ON E.EmployeeID = B.EmployeeID;

Always verify your join condition when using UPDATE from another table. An incorrect join can update many more rows than intended, or even every row in the target table.

3. Using Subqueries in INSERT, UPDATE, and DELETE

Subqueries, covered conceptually in the previous module, let you express DML logic that depends on values in other tables or filtered sets of rows.

INSERT Using a Subquery

INSERT INTO TopPerformers (EmployeeID, FullName)
SELECT  EmployeeID,
        FirstName + N' ' + LastName
FROM    Employees
WHERE   EmployeeID IN (
            SELECT EmployeeID
            FROM   Performance
            WHERE  Rating = 'A'
       );

UPDATE Using a Subquery

UPDATE Employees
SET    Salary = Salary * 1.10
WHERE  DepartmentID = (
           SELECT DepartmentID
           FROM   Departments
           WHERE  DepartmentName = N'Engineering'
       );

DELETE Using a Subquery

DELETE FROM Employees
WHERE  EmployeeID IN (
           SELECT EmployeeID
           FROM   Resignations
           WHERE  ResignDate < GETDATE()
       );

When you use subqueries in a WHERE clause, keep two things in mind:

• A subquery used in a scalar context (= (SELECT ...)) must return a single value.
• Use IN or EXISTS instead whenever the subquery could return multiple rows.

4. Optimizing and Safeguarding DML Statements

This module introduces the basics of DML, but it's worth starting with sound habits that scale to production workloads.

General Optimization Guidelines

• Create and maintain indexes on columns used in WHERE, JOIN, and ON clauses.
• Prefer set-based operations over row-by-row logic.
• Avoid unnecessary triggers or cascading updates unless there's a clear business requirement.
• Update or delete in smaller batches to reduce locking and blocking on busy systems.

Example: Deleting in Batches

WHILE 1 = 1
BEGIN
    DELETE TOP (1000)
    FROM   Logs
    WHERE  LogDate < DATEADD(YEAR, -1, GETDATE());

    IF @@ROWCOUNT = 0 BREAK;
END;

This pattern removes old rows gradually, which is safer for large tables than one massive DELETE statement.

Using MERGE for More Complex Logic

MERGE can combine insert and update logic into a single statement:

MERGE INTO Employees AS Target
USING NewSalaries AS Source
    ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Target.Salary = Source.NewSalary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Salary)
    VALUES (Source.EmployeeID, Source.NewSalary);

MERGE has a long, documented history of edge-case bugs in SQL Server, some fixed over the years, some still open. The most practical one to know about: without a HOLDLOCK hint on the target table, a MERGE statement is vulnerable to race conditions under concurrent access; two sessions can both decide a row is "not matched" and both attempt an insert. If you use MERGE in a concurrent environment, apply WITH (HOLDLOCK) to the target. For many scenarios, separate INSERT and UPDATE statements remain easier to reason about, debug, and keep safe under concurrency, so use MERGE deliberately rather than by default.

Measuring the Cost of Your Queries

SQL Server provides tools to help you evaluate the impact of your DML statements:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run your DML statement(s) here.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

For deeper analysis, inspect the execution plan in SQL Server Management Studio (SSMS) to see how indexes and joins are actually being used.

When inserting, updating, or deleting data, SQL Server enforces business rules through constraints and default definitions. If a DML operation violates a rule, the change is rejected.

Historically, SQL Server supported separate objects called rules and standalone default objects, created with CREATE RULE and CREATE DEFAULT. These have been marked for removal since SQL Server 2005 and are kept only for backward compatibility; they are still present in SQL Server 2025, but Microsoft has never set a firm removal date. The fact that they've survived two decades of "future version" warnings is not a reason to start using them in new work. In modern development, use:

• CHECK constraints to enforce valid ranges and allowed values.
• FOREIGN KEY constraints to maintain referential integrity.
• DEFAULT constraints defined directly on columns to supply default values.

Defaults serve a simple but important purpose: if you insert a row and omit a column that has a default defined, SQL Server automatically supplies the default value.

In the next lesson, you will take a closer look at the INSERT statement and see how these ideas combine when adding data to your tables.