Adding, changing and removing rows

INSERT, UPDATE and DELETE, plus the one habit that stops you deleting the wrong data.

Everything so far has read data. These three statements change it. Analysts read far more than they write, but you’ll still use them on your own tables and in practice databases.

INSERT: add rows

INSERT INTO accounts (account_id, name, industry, segment)
VALUES (501, 'Lakeside Dental Group', 'Healthcare', 'SMB');

List the columns, then the values in the same order. Columns you leave out get their default value, or NULL.

Several rows at once:

INSERT INTO accounts (account_id, name, industry, segment)
VALUES (502, 'Pine Ridge Health', 'Healthcare', 'SMB'),
       (503, 'Granite Logistics', 'Transportation', 'Mid-Market');

UPDATE: change rows

UPDATE accounts
SET segment = 'Mid-Market'
WHERE account_id = 501;

DELETE: remove rows

DELETE FROM opportunities
WHERE name LIKE 'TEST%';

The danger: forgetting WHERE

UPDATE and DELETE without a WHERE change every row in the table:

UPDATE accounts SET segment = 'Mid-Market';   -- every account is now Mid-Market
DELETE FROM opportunities;                    -- every deal is gone

There’s no confirmation prompt and usually no undo.

The habit: SELECT first

Before any UPDATE or DELETE, run the same WHERE as a SELECT:

-- 1. See exactly what you're about to delete, and how many rows
SELECT opportunity_id, name
FROM opportunities
WHERE name LIKE 'TEST%';
 
-- 2. Happy with the list? Swap the first line
DELETE FROM opportunities
WHERE name LIKE 'TEST%';

If step 1 returns 3 rows, step 2 should report 3 rows deleted. If the numbers differ, stop and find out why.

Good to know: on production systems, analysts often have read-only access, so these statements fail with a permissions error. That’s deliberate. Practice them on your own copy of the data.

Try it

You’ve been asked to close out every open deal in the Prospecting stage that was created before 2026. Write the SELECT you’d run first to see exactly which deals that is. Return opportunity_id, name and created_at.

Write it in the sandbox below, then press Check answer. Once it passes, try the matching UPDATE that sets their stage to 'Closed Lost', then run your SELECT again. Reset puts the sample data back.

Back to the top of the lesson