SQL DML Commands: INSERT, UPDATE, DELETE, and TRUNCATE

Data Manipulation Language (DML) encompasses the core SQL commands used to modify, insert, update, and remove data stored within database tables. Unlike Data Query Language (DQL) commands like SELECT—which merely retrieve and read data—DML statements directly alter the state of the database engine's underlying tables.

1. DML vs. DQL: Understanding Direct Data Modification

When querying a database using a SELECT statement, the database engine returns a result set formatted as a virtual table. It does not alter stored records. DML commands (INSERT, UPDATE, DELETE), by contrast, execute direct write, rewrite, or drop operations on disk storage.

Execution Feedback

When executing a DML statement, SQL engines do not return a data grid. Instead, they return a status execution message detailing the scope of the operation:

Database Feedback: 2 rows affected
(Note: "Affected" signifies records that were inserted, modified, or removed during statement execution.)

2. Manual Data Insertion (INSERT INTO ... VALUES)

The standard mechanism for appending new rows into an existing database table is the manual INSERT INTO statement utilizing explicit values.

Universal Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Basic Single and Multi-Row Examples

Inserting new customer records into a customers table containing ID, first_name, country, and score:

-- Inserting two new rows in a single execution statement
INSERT INTO customers (ID, first_name, country, score)
VALUES 
    (6, 'Anna', 'USA', NULL),
    (7, 'Sam', NULL, 100);

3. Insertion Rules, Constraints, and Data Type Pitfalls

Rule 1: Structural Cardinality Match

The total count of columns declared in the target column list must exactly match the number of items supplied inside each VALUES (...) clause.

-- INVALID: Column count (3) does not match Value count (2)
INSERT INTO customers (ID, first_name, country)
VALUES (8, 'Alex'); -- Throws Syntax / Cardinality Error

Rule 2: Primary Keys and NOT NULL Constraints

If a column is defined with a NOT NULL constraint or as a Primary Key, passing NULL explicitly or omitting the column will trigger a database engine rejection.

-- REJECTED BY DATABASE: ID cannot be NULL
INSERT INTO customers (ID, first_name, country, score)
VALUES (NULL, 'Anna', 'USA', 0);
-- Output Error: Cannot insert the value NULL into column 'ID'.

Rule 3: Column Order & Type-Checking Blindspots

Database engines enforce strict Data Type Constraints, but they cannot enforce Semantic Meaning. If two columns share the same data type (e.g., two VARCHAR fields), swapping their positions in the VALUES clause will result in corrupted data insertion without throwing an error.

-- SEMANTIC ERROR: Swapped first_name ('USA') and country ('Max')
INSERT INTO customers (ID, first_name, country, score)
VALUES (8, 'USA', 'Max', 50);
-- Database accepts this because both 'USA' and 'Max' are valid string data types!

Conversely, attempting to place non-numeric characters inside integer fields causes an immediate engine rejection:

-- TYPE ERROR: Supplying string 'Max' to integer ID field
INSERT INTO customers (ID, first_name, country, score)
VALUES ('Max', 9, 'Germany', 100);
-- Output Error: Conversion failed when converting the varchar value 'Max' to data type int.

Rule 4: Skipping Optional Column Lists (Implicit Insert)

If you intend to supply values for every single column in the exact order defined by the database schema, the explicit column list can be omitted.

-- Implicit Insert (Requires precise schema-order matching for all fields)
INSERT INTO customers
VALUES (9, 'Andreas', 'Germany', NULL);

Rule 5: Partial Column Insertion (Defaulting to NULL)

To supply data for specific columns while leaving optional fields blank, list only the target columns. Unspecified columns will automatically populate with NULL (or their default values).

-- Supplying only ID and first_name; country and score default to NULL
INSERT INTO customers (ID, first_name)
VALUES (10, 'Sara');

4. Dynamic Data Migration (INSERT INTO ... SELECT)

Rather than typing values manually, SQL allows populating a target table dynamically by selecting data directly from a source table.

Real-Life Analogy

Imagine moving physical file folders from an old filing cabinet (Source Table) into a newly restructured archiving cabinet (Target Table). Instead of retyping every page by hand (Manual Insert), you photocopy the relevant pages from the source cabinet, re-label the folder tabs to match the new cabinet’s layout, and drop them straight into place.

The 2-Step Migration Strategy

1. Step 1: Write and refine the SELECT query on the source table.
   Shape the query output so the column count, position, and data types match the target schema. Use hardcoded static values or NULL literals for columns missing in the source table.
2. Step 2: Wrap the validated SELECT query inside an INSERT INTO statement.

Implementation Code Example

Assume target table persons schema requires: ID (int, NOT NULL), person_name (varchar, NOT NULL), birth_date (date, NULL allowed), and phone (varchar, NOT NULL).

-- Step 1 & 2 Combined: Migrating filtered customer records into persons table
INSERT INTO persons (ID, person_name, birth_date, phone)
SELECT 
    ID,                 -- Maps to target 'ID'
    first_name,         -- Maps to target 'person_name'
    NULL AS birth_date, -- Source lacks birth dates; explicit NULL injected
    'unknown' AS phone  -- Target requires NOT NULL; static fallback string injected
FROM customers;

Note: The database maps data strictly by position and compatible data types. Source column names (or column aliases) do not need to match target column names.

5. Modifying Existing Records (UPDATE)

The UPDATE command modifies existing values within a table without inserting new rows or dropping existing schema structures.

Universal Syntax

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE filter_condition;

Production Risk: The Unfiltered Update

Executing an UPDATE statement without a WHERE clause causes the engine to apply changes across every single row in the table.

-- DANGER: Missing WHERE clause updates ALL customers!
UPDATE customers
SET score = 0; 
-- Result: Every customer's score in the entire database becomes 0!

The Professional Safe Execution Workflow

To prevent catastrophic accidental data overwrites in production environments, follow this standard query verification sequence:

Step 1: Write Verification Query
   |  SELECT * FROM customers WHERE ID = 6;
   v
Step 2: Inspect Returned Rows
   |  (Verify that ONLY intended record(s) are listed)
   v
Step 3: Convert SELECT to UPDATE Statement
   |  UPDATE customers SET score = 0 WHERE ID = 6;
   v
Step 4: Execute & Verify Affected Rows Count

SQL Implementation

-- Step 1: Verification Query
SELECT * FROM customers 
WHERE ID = 6;

-- Step 2: Executing safe update after confirming target row
UPDATE customers
SET score = 0
WHERE ID = 6;

6. Multi-Column and Bulk Subset Updates

Updating Multiple Columns Simultaneously

To update multiple fields for a single target record, separate the assignments within the SET clause using commas.

-- Updating both score and country for Customer ID 10
UPDATE customers
SET score = 0,
    country = 'UK'
WHERE ID = 10;

Updating Subsets of Data (Bulk Updates)

UPDATE statements can modify multiple records at once when paired with a WHERE condition that matches a group of rows.

-- Step 1: Inspect target subset (All records with missing scores)
SELECT * FROM customers 
WHERE score IS NULL;

-- Step 2: Bulk update score for all matching subset records
UPDATE customers
SET score = 0
WHERE score IS NULL;
-- Execution Output: 2 rows affected.

7. Row Removal (DELETE FROM)

The DELETE statement removes specific rows from a database table based on conditional filtering.

Universal Syntax

DELETE FROM table_name
WHERE filter_condition;

Production Risk: Missing WHERE Clause

Executing DELETE FROM table_name; without a WHERE clause clears all data rows within the target table.

Verification and Deletion Workflow

-- Step 1: Test filter criteria using a SELECT statement
SELECT * FROM customers 
WHERE ID > 5;

-- Step 2: Execute DELETE command matching identical filter criteria
DELETE FROM customers
WHERE ID > 5;
-- Execution Output: 5 rows affected.

8. Emptying Tables: DELETE FROM vs. TRUNCATE TABLE

When tasked with clearing all records from a table while retaining the table's structure, database engines offer two distinct methods: DELETE FROM and TRUNCATE TABLE.

-- Method A: Deleting all rows via DML
DELETE FROM persons;

-- Method B: Truncating the entire table via DDL/Optimized operation
TRUNCATE TABLE persons;

Architectural Comparison

9. Comprehensive DML Comparison Matrix

Summary and Key Takeaways

• DML Alters Stored Data: DML commands (INSERT, UPDATE, DELETE) alter table records on disk and return execution messages (e.g., X rows affected) rather than data grids.
• INSERT INTO ... VALUES Match Rules: Column count and value count must align perfectly. Primary keys and NOT NULL columns cannot accept NULL insertions.
• Type Checking vs. Order Errors: Database engines reject data type mismatches (e.g., string inserted into an integer column), but will accept swapped string columns without throwing errors.
• Dynamic Table-to-Table Migration: INSERT INTO ... SELECT allows migrating data from a source table to a target table without manual entry scripts. Columns map by position and type compatibility.
• Strict UPDATE Precautions: Always verify target records using SELECT * FROM table WHERE condition prior to running an UPDATE command to prevent overwriting an entire table.
• Multi-Column Updates: Assign updates across multiple columns within a single UPDATE query by separating key-value pairs with commas.
• Conditional DELETE Execution: Removing specific records requires explicit WHERE filtering to avoid unintended data loss.
• TRUNCATE for Mass Table Clearing: Use TRUNCATE TABLE instead of an unfiltered DELETE FROM query to clear all data from a table quickly while skipping row-by-row logging overhead.