Lesson 6: INSERT / UPDATE / DELETE and Safe Patterns (Write Operations the DBA Way)

Section 3 — Lesson 6: INSERT / UPDATE / DELETE and Safe Patterns (Write Operations the DBA Way)

Welcome back. Up to this point, most of your work has been read-only (SELECT).
That is good: DBAs always start by reading, validating, and measuring.

Now we move to the most sensitive category of SQL work: writing changes.
INSERT, UPDATE, and DELETE can fix problems—or they can destroy production data if used carelessly.
The difference between a beginner and a job-ready DBA is not “knowing the commands.”
The difference is having safe patterns that prevent disasters.

---

Lesson description: You will learn how INSERT, UPDATE, and DELETE work,
how to write them safely with validation-first workflows, how to prevent accidental mass updates/deletes,
how to use transactions for controlled changes, how to confirm impact before and after,
and how DBAs document and execute change operations in real environments.

---

1) The DBA “Safe Write” Workflow (Non-Negotiable)

Before we write any SQL that changes data, you need a professional workflow.
This is what DBAs do in real organizations—especially in production:

1. Identify target rows with SELECT (prove you are touching the correct records).
2. Estimate impact (how many rows will change?).
3. Back up / ensure recoverability (in production: confirmed backup/restore plan).
4. Use a transaction for controlled changes (BEGIN TRAN / COMMIT / ROLLBACK).
5. Re-run the SELECT after the change (validate the outcome).
6. Document what you changed (ticket, timestamp, reason, evidence).

DBA rule: You never start with UPDATE/DELETE. You start with SELECT.

---

2) INSERT (Adding New Rows)

INSERT creates new rows in a table. In real systems, INSERTs often come from applications,
but DBAs still perform INSERTs during testing, data fixes, migrations, and lab work.

2A) Always list columns explicitly (do not rely on column order)

Listing columns protects you if a column is added later, or if defaults change.

-- Insert a new customer (example)
INSERT INTO dbo.Customer (FullName, Email, Phone, CreatedAt)
VALUES (N'Jordan Smith', N'jordan@example.com', N'716-555-0123', SYSDATETIME());
  

2B) Insert multiple rows (bulk style)

INSERT INTO dbo.Service (ServiceName, BasePriceUSD, IsActive)
VALUES
(N'BUF to Niagara Falls (Canada)', 95.00, 1),
(N'BUF to Niagara Falls (USA)', 85.00, 1);
  

2C) Capture the identity of an inserted row (SCOPE_IDENTITY)

In many systems, tables use IDENTITY keys. After inserting, you often need the new ID.

DECLARE @NewCustomerID INT;

INSERT INTO dbo.Customer (FullName, Email, Phone, CreatedAt)
VALUES (N'Taylor Lee', N'taylor@example.com', NULL, SYSDATETIME());

SET @NewCustomerID = CAST(SCOPE_IDENTITY() AS INT);

SELECT @NewCustomerID AS NewCustomerID;
  

DBA note: Use SCOPE_IDENTITY() (not @@IDENTITY) to avoid cross-scope surprises.

---

3) UPDATE (Changing Existing Rows)

UPDATE modifies existing rows. This is where disasters happen if you forget a WHERE clause
or filter incorrectly.

3A) The “SELECT first, then UPDATE” pattern

Let’s say you need to change a booking status from NEW to CONFIRMED for a specific confirmation code.

-- Step 1: Prove the target rows (read-only)
SELECT BookingID, ConfirmationCode, Status, PickupAt
FROM dbo.Booking
WHERE ConfirmationCode = 'BUF638025';
  

If the SELECT returns the exact row(s) you expect, then do the UPDATE.

-- Step 2: Update using the same filter
UPDATE dbo.Booking
SET Status = 'CONFIRMED'
WHERE ConfirmationCode = 'BUF638025';
  

3B) Always verify how many rows were affected

SQL Server returns “(X rows affected)”. DBAs treat that as evidence.
If you expected 1 and you got 10, stop and investigate immediately.

3C) Update with additional safety checks

You can make updates safer by adding more conditions that must be true.
Example: only update if current status is NEW.

UPDATE dbo.Booking
SET Status = 'CONFIRMED'
WHERE ConfirmationCode = 'BUF638025'
  AND Status = 'NEW';
  

DBA advantage: This prevents updating records that are already in another state unexpectedly.

---

4) DELETE (Removing Rows) and Why DBAs Are Extremely Careful

DELETE removes rows. In production, deletes are often restricted because they can destroy history.
Many systems prefer “soft delete” (mark inactive) rather than physical deletion.

4A) The safe deletion workflow starts with SELECT

-- Step 1: prove the target row(s)
SELECT *
FROM dbo.Customer
WHERE Email = 'oldtest@example.com';
  

-- Step 2: delete using the same filter (only if allowed and safe)
DELETE FROM dbo.Customer
WHERE Email = 'oldtest@example.com';
  

DBA warning: If the row is referenced by foreign keys, the DELETE may fail
(good!) or it may cascade (dangerous if misused).

4B) Prefer “soft delete” when history matters

Instead of deleting a service, mark it inactive so historical bookings remain valid.

UPDATE dbo.Service
SET IsActive = 0
WHERE ServiceName = N'Old Service Name';
  

DBA note: Soft-delete is common in real systems because it preserves auditability.

---

5) The Single Most Important Safety Tool: Transactions

A transaction lets you group changes and either:

• COMMIT them permanently, or
• ROLLBACK them to undo the changes

DBAs use transactions for controlled change scripts and for emergency fixes.

5A) Update inside a transaction with validation

BEGIN TRAN;

-- Validate before change
SELECT BookingID, ConfirmationCode, Status
FROM dbo.Booking
WHERE ConfirmationCode = 'BUF638025';

-- Apply change
UPDATE dbo.Booking
SET Status = 'CONFIRMED'
WHERE ConfirmationCode = 'BUF638025'
  AND Status = 'NEW';

-- Validate after change (still inside the transaction)
SELECT BookingID, ConfirmationCode, Status
FROM dbo.Booking
WHERE ConfirmationCode = 'BUF638025';

-- If everything looksClooks right, commit. If not, rollback.
-- COMMIT TRAN;
-- ROLLBACK TRAN;
  

DBA practice: In training/lab, run the script, inspect results, then choose COMMIT or ROLLBACK.
In production, this decision is governed by change control and approvals.

5B) Use TRY/CATCH for safer scripts (preview concept)

You will learn TRY/CATCH later, but DBAs often wrap data-change scripts to ensure rollback on error.

BEGIN TRY
    BEGIN TRAN;

    UPDATE dbo.Booking
    SET Status = 'CONFIRMED'
    WHERE ConfirmationCode = 'BUF638025'
      AND Status = 'NEW';

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;
    THROW;
END CATCH;
  

DBA note: This is a production-grade safety pattern when used with proper validation and logging.

---

6) “Safe Patterns” That Prevent Real Production Incidents

Pattern A: Always include a WHERE clause for UPDATE and DELETE

It sounds obvious, but many real incidents start with:
UPDATE dbo.Table SET … with no WHERE.

Pattern B: Use a key-based filter whenever possible

Prefer filtering on stable keys like BookingID or ConfirmationCode,
not fuzzy text like names.

Pattern C: Add “current state” conditions (defensive updates)

Example: only update if the row is in the expected status.

Pattern D: Make changes in small batches for large updates

For very large tables, DBAs often update in chunks to reduce blocking, log growth, and transaction risk.
(You will revisit this concept later with locking and log internals.)

-- Example batch update pattern (concept)
WHILE 1 = 1
BEGIN
    UPDATE TOP (1000) dbo.SomeLargeTable
    SET SomeFlag = 1
    WHERE SomeFlag = 0;

    IF @@ROWCOUNT = 0 BREAK;
END;
  

DBA caution: This is an advanced operational pattern; in real production you also add logging and wait delays.

Pattern E: Use OUTPUT for evidence (audit your change)

OUTPUT can return what changed—useful for logging, confirmation, or rollback planning.

-- See old and new values for an update (evidence)
UPDATE dbo.Booking
SET Status = 'CONFIRMED'
OUTPUT deleted.BookingID, deleted.Status AS OldStatus, inserted.Status AS NewStatus
WHERE ConfirmationCode = 'BUF638025'
  AND Status = 'NEW';
  

DBA note: OUTPUT is powerful for “prove what changed” requirements.

---

7) Practice (Job-Ready Exercises)

Perform these in your lab. Use the safe workflow: SELECT first, transaction, validate.

1. Insert a new customer with FullName, Email, and CreatedAt. Verify by selecting the inserted row.
2. Insert a new booking tied to that customer (requires a valid ServiceID). Verify the booking exists.
3. Update the booking status from NEW to CONFIRMED using a key filter and a “current state” condition.
4. Wrap the update inside a transaction, validate before and after, then COMMIT.
5. Create a “soft delete” update by setting IsActive = 0 for a service (do not delete the row).
6. (Optional) Use OUTPUT on an update to capture old/new status values.

---

Lesson Summary

• INSERT adds new rows; always list columns explicitly and capture identity when needed.
• UPDATE changes existing rows; always SELECT first and filter with keys.
• DELETE removes rows and can destroy history; prefer soft delete when appropriate.
• Transactions (BEGIN/COMMIT/ROLLBACK) are the core safety tool for controlled changes.
• Production-grade safety patterns include defensive WHERE conditions, batching, OUTPUT evidence, and TRY/CATCH rollback.

---

Next Lesson: Temp Tables vs Table Variables (Practical DBA Usage)

Next, you will learn when DBAs use temporary storage during troubleshooting and reporting:
#temp tables, table variables, and how to choose the right tool for performance and reliability.