T-SQL: Data Manipulation Language (DML)

Basics of DML - Data Manipulation Language

Think CRUD: create, read, update, delete

SQL clauses operate in layers: Target → Sources → Filters.
(Some clauses shape data, others filter it.)

• The target is what the statement acts on
• FROM / JOIN define the data sources
• WHERE filters rows
• GROUP BY defines aggregation boundaries
• HAVING filters groups

The target is defined by the main statement keyword

Statement Written Order:

• SELECT: SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY
• UPDATE: UPDATE → SET → FROM → WHERE
• INSERT: INSERT INTO → VALUES / SELECT
• DELETE: DELETE → FROM → WHERE

Written order is NOT execution order. SQL is declarative, not procedural. (You describe what you want, not how to get it.)

Basic SELECT Pattern

1
2
3
4
5
6
7

SELECT
    t.col1,
    t.col2,
    t.col3
FROM dbo.MyTable AS t
WHERE t.col1 = 'some value'
ORDER BY t.col2;

SELECT with JOIN pattern

1
2
3
4
5
6
7

SELECT
    a.Id,
    a.Name,
    b.Status
FROM dbo.TableA AS a
JOIN dbo.TableB AS b
    ON b.TableAId = a.Id;

SELECT with GROUP BY / HAVING

1
2
3
4
5
6

SELECT
    Category,
    COUNT(*) AS RecordCount
FROM dbo.Items
GROUP BY Category
HAVING COUNT(*) > 1;

INSERT with SELECT

Data comes from another query

1
2
3
4

INSERT INTO dbo.ArchiveTable (Id, [Name])
SELECT Id, [Name]
FROM dbo.SourceTable AS s
WHERE s.IsActive = 0;

Note: Each row returned by the SELECT is inserted into dbo.ArchiveTable.

INSERT with values

You already know the data

1
2

INSERT INTO dbo.Users ([Name], Email)
VALUES ('Bob', 'bob@example.com');

Helpful Notes

Before writing an UPDATE or DELETE, first write a SELECT using the same WHERE clause. Verify the affected rows, then convert it to UPDATE or DELETE.

We omit the keyword “AS” for UPDATE and DELETE statements. In SQL Server, UPDATE and DELETE don’t treat the table after the keyword as a data source. They treat it as a target object. And targets cannot use AS.

UPDATE with JOIN pattern

1
2
3
4
5
6
7

UPDATE a
SET a.Status = b.NewStatus
FROM dbo.TableA a
JOIN dbo.TableB b
    ON b.TableAId = a.Id
WHERE b.IsActive = 1
  AND a.Status <> b.NewStatus;

DELETE with JOIN pattern

1
2
3
4
5

DELETE a
FROM dbo.TableA a
JOIN dbo.TableB b
    ON b.TableAId = a.Id
WHERE b.IsExpired = 1;