MAN456 · DATA MANAGEMENT
Writing SQL Reference INSERT UPDATE DELETE
ONE-PAGE CHEAT SHEET · INSERT · UPDATE · DELETE
Read every DML query before you run it. Keep this open in the lab.
1 THE THREE VERBS 2 KEYWORDS · one-line meanings
Three skeletons cover almost every change. INSERT INTO name the table to add rows to
VALUES the values, in the same column order
INSERT INTO ... add new rows
UPDATE name the table whose rows you change
VALUES (...) the new values
UPDATE ... change existing rows SET list new column values: SET col = expr
SET col = ... new column values DELETE FROM name the table to remove rows from
DELETE FROM ... remove rows WHERE which rows - required for UPDATE /
WHERE ... the safety clause DELETE
RETURNING (some DBs) show rows that were changed
READING ORDER (UPDATE & DELETE)
Don't read top to bottom. Read like this:
1 WHERE which rows will be touched?
2 SET / VALUES what is the new value?
3 table is this the right table?
3 INSERT · add new rows 4 UPDATE · change rows
List columns, give values in the same order. Pick the table, set the new value - always say WHERE.
Add one row Change one column for matching rows
INSERT INTO Customers UPDATE Products
(CompanyName, City, Country) SET UnitPrice = UnitPrice * 1.10
VALUES ('Acme', 'London', 'UK'); WHERE CategoryID = 1;
Add many rows at once Change several columns at once
INSERT INTO Categories (CategoryName) UPDATE Customers
VALUES ('Beverages'), SET City = 'Istanbul', Country = 'TR'
('Condiments'), ('Seafood'); WHERE CustomerID = 'ALFKI';
Copy rows from another table Compute the new value from the old one
INSERT INTO ArchiveOrders (OrderID, OrderDate) UPDATE Employees
SELECT OrderID, OrderDate FROM Orders SET Salary = Salary + 500
WHERE OrderDate < '2020-01-01'; WHERE DepartmentID = 3;
5 DELETE · remove rows 6 THE PREVIEW PATTERN · every time
Pick the table. Say WHERE. Always. Same WHERE, twice: a question, then an action.
Remove matching rows STEP 1 PREVIEW STEP 2 RUN
SELECT * DELETE
DELETE FROM Orders
FROM Orders FROM Orders
WHERE OrderDate < '2020-01-01';
WHERE OrderDate WHERE OrderDate
< '2020-01-01'; < '2020-01-01';
-> shows what you'd touch -> same WHERE - now it's gone
DELETE FROM Orders; - no WHERE
! ASKING AI FOR DML
...empties the entire table. Same warning for UPDATE
without WHERE - every row gets the new value. 1. "Using this schema:"
-> paste table & column names
2. "Write SQL to ... (state goal clearly)"
-> include WHICH rows
Foreign keys may block your DELETE
3. "Show the WHERE as a SELECT first"
If other tables reference the row (e.g. Order Details -> Orders), the -> preview before changing
database refuses the DELETE unless ON DELETE CASCADE is
4. "Explain the query line by line."
set. Read the error - it names the constraint. -> make AI prove it understood
✓ Always check the SQL yourself.
Is there a WHERE? Is it the right one? Then run it.
MAN456 · Data Management · Yalova University · Designed for the Northwind sample database