DML Commands

Working with Data

DML (Data Manipulation Language) commands modify the actual data in your tables — inserting new records, updating existing ones, and deleting rows.

INSERT

  -- Insert one row
  INSERT INTO Students (ID, Name, Age, Email, DeptID)
  VALUES (1, 'Alice', 25, 'alice@example.com', 10);

  -- Insert multiple rows
  INSERT INTO Students (ID, Name, Age, Email, DeptID) VALUES
  (2, 'Bob', 22, 'bob@example.com', 20),
  (3, 'Charlie', 23, 'charlie@example.com', 10);

  -- Insert from another table
  INSERT INTO StudentBackup (ID, Name)
  SELECT ID, Name FROM Students WHERE Age > 22;

UPDATE

  -- Update a single row
  UPDATE Students SET Age = 26 WHERE ID = 1;

  -- Update multiple columns
  UPDATE Students SET Age = 26, Email = 'alice.new@example.com' WHERE ID = 1;

  -- Update multiple rows
  UPDATE Students SET DeptID = 10 WHERE DeptID = 30;

  -- WARNING: Without WHERE, ALL rows are updated!
  UPDATE Students SET Age = 0;  -- Don't do this!

DELETE

  -- Delete a specific row
  DELETE FROM Students WHERE ID = 3;

  -- Delete based on condition
  DELETE FROM Students WHERE Age < 18;

  -- Delete all rows (keeps structure)
  DELETE FROM Students;

  -- WARNING: Without WHERE, ALL rows are deleted!
  DELETE FROM Students;  -- Deletes everything!

MERGE / UPSERT

MERGE combines INSERT and UPDATE — if the row exists, update it; if not, insert it:

  -- PostgreSQL
  INSERT INTO Students (ID, Name, Age)
  VALUES (1, 'Alice Updated', 26)
  ON CONFLICT (ID)
  DO UPDATE SET Name = EXCLUDED.Name, Age = EXCLUDED.Age;

  -- MySQL
  INSERT INTO Students (ID, Name, Age)
  VALUES (1, 'Alice Updated', 26)
  ON DUPLICATE KEY UPDATE Name = VALUES(Name), Age = VALUES(Age);