CodeToLive

SQL Data Modification (DML)

Data Manipulation Language (DML) includes commands for inserting, updating, deleting, and merging data in database tables. These operations are fundamental for maintaining and changing data in your database.

Sample Table for Examples

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2),
    department_id INT,
    hire_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    manager_id INT
);
        

INSERT Statement

Add new rows to a table:

Basic INSERT

-- Insert single row with all columns
INSERT INTO employees 
VALUES (101, 'John', 'Doe', 'john.doe@example.com', 75000, 10, '2020-01-15');

-- Insert with specified columns
INSERT INTO employees (employee_id, first_name, last_name, email, department_id)
VALUES (102, 'Jane', 'Smith', 'jane.smith@example.com', 20);
        

Multi-row INSERT

-- Insert multiple rows in one statement
INSERT INTO employees 
VALUES 
    (103, 'Robert', 'Johnson', 'robert.j@example.com', 85000, 10, '2019-05-22'),
    (104, 'Emily', 'Davis', 'emily.d@example.com', 65000, 30, '2021-02-28'),
    (105, 'Michael', 'Brown', 'michael.b@example.com', 80000, 20, '2020-07-19');
        

INSERT from SELECT

-- Copy data from another table
INSERT INTO employees_backup
SELECT * FROM employees
WHERE hire_date > '2020-01-01';
        

UPDATE Statement

Modify existing data in a table:

Basic UPDATE

-- Update all rows
UPDATE employees
SET salary = salary * 1.05;  -- Give everyone 5% raise

-- Update specific rows
UPDATE employees
SET salary = 80000
WHERE employee_id = 104;
        

UPDATE with JOIN

-- Update based on another table
UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.10
WHERE d.department_name = 'IT';
        

UPDATE with Subquery

-- Set manager for IT department
UPDATE departments
SET manager_id = (
    SELECT employee_id 
    FROM employees 
    WHERE department_id = 30 
    ORDER BY salary DESC 
    LIMIT 1
)
WHERE department_name = 'IT';
        

DELETE Statement

Remove rows from a table:

Basic DELETE

-- Delete specific rows
DELETE FROM employees
WHERE department_id = 30;

-- Delete all rows (be careful!)
DELETE FROM employees_backup;
        

DELETE with JOIN

-- Delete based on another table
DELETE e FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'HR';
        

MERGE Statement (UPSERT)

Combine INSERT and UPDATE operations:

-- MySQL/PostgreSQL syntax (INSERT ... ON DUPLICATE KEY UPDATE)
INSERT INTO employees (employee_id, first_name, last_name, email, salary)
VALUES (106, 'Sarah', 'Wilson', 'sarah.w@example.com', 72000)
ON DUPLICATE KEY UPDATE
    first_name = VALUES(first_name),
    last_name = VALUES(last_name),
    email = VALUES(email),
    salary = VALUES(salary);

-- SQL Server/Oracle syntax
MERGE INTO employees e
USING (SELECT 107 AS emp_id, 'David' AS fname, 'Lee' AS lname) AS src
ON e.employee_id = src.emp_id
WHEN MATCHED THEN
    UPDATE SET first_name = src.fname, last_name = src.lname
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name)
    VALUES (src.emp_id, src.fname, src.lname);
        

TRUNCATE TABLE

Remove all rows quickly (DDL command, not DML):

-- Faster than DELETE for large tables
TRUNCATE TABLE employees_backup;
        

Transaction Control

Group DML operations into transactions:

START TRANSACTION;

INSERT INTO departments VALUES (40, 'HR', NULL);
UPDATE employees SET department_id = 40 WHERE employee_id = 104;

-- Only commit if both operations succeed
COMMIT;

-- Or rollback on error
-- ROLLBACK;
        

Common DML Scenarios

Bulk Data Loading

-- MySQL
LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- PostgreSQL
COPY employees FROM '/path/to/employees.csv' DELIMITER ',' CSV HEADER;
        

Conditional Updates

-- Give different raises based on salary
UPDATE employees
SET salary = CASE
    WHEN salary < 60000 THEN salary * 1.10
    WHEN salary BETWEEN 60000 AND 80000 THEN salary * 1.07
    ELSE salary * 1.05
END;
        

Archiving Data

-- Move old records to archive table
INSERT INTO employees_archive
SELECT * FROM employees
WHERE hire_date < '2018-01-01';

DELETE FROM employees
WHERE hire_date < '2018-01-01';
        

DML Best Practices

• Always use WHERE clauses with UPDATE and DELETE unless you intend to affect all rows
• Test DML statements with SELECT first
• Use transactions for multiple related operations
• Consider adding indexes for frequently filtered columns
• Backup important data before major DML operations
• Use batch operations for large data changes

DML Performance Considerations