Inserting, Updating, and Deleting Data (DML)

Now the other half of managing data: adding, changing, and removing rows.

INSERT — adding rows

-- One row, all columns in table order
INSERT INTO departments VALUES (7, 'Legal', 'Karachi');

-- One row, naming columns explicitly (recommended — safer if columns ever change order)
INSERT INTO departments (id, name, location) VALUES (7, 'Legal', 'Karachi');

-- Multiple rows in one statement
INSERT INTO departments (id, name, location) VALUES
  (8, 'Research', 'Islamabad'),
  (9, 'Procurement', 'Lahore');

INSERT from a SELECT

You can populate a table directly from a query — very useful for copying or archiving data:

CREATE TABLE lahore_departments AS
SELECT * FROM departments WHERE location = 'Lahore';

UPDATE — changing existing rows

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1;

The most important rule of UPDATE: always include a WHERE clause. Without one, every single row in the table gets updated:

-- DANGER: this gives EVERY employee a raise, not just one department
UPDATE employees SET salary = salary * 1.10;

UPDATE with a subquery

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

DELETE — removing rows

DELETE FROM orders WHERE status = 'cancelled';

Same rule applies: always use WHERE. DELETE FROM orders; with no WHERE removes every row in the table.

A safe habit: SELECT before you UPDATE/DELETE

Before running an UPDATE or DELETE, run the same condition as a SELECT first, to see exactly which rows will be affected:

-- Step 1: check what you're about to change
SELECT * FROM orders WHERE status = 'cancelled';

-- Step 2: once you're confident, run the real statement
DELETE FROM orders WHERE status = 'cancelled';

This habit alone will save you from the single most common — and most painful — SQL mistake.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;