Manipulating Data: INSERT, UPDATE, and DELETE Commands

Adding, changing, and removing data in MySQL. The INSERT, UPDATE, and DELETE commands you need for any database-driven application.

INSERT: adding new data

INSERT adds new rows to a table.

Basic INSERT

With a customers table, adding a new customer:

INSERT INTO customers (name, email, join_date) 
VALUES ('John Doe', 'john@example.com', '2022-07-15');

That inserts one row with the values you specified.

Multiple records at once

You can insert several rows in a single statement:

INSERT INTO customers (name, email, join_date) 
VALUES ('Jane Smith', 'jane@example.com', '2022-07-16'),
       ('Alice Johnson', 'alice@example.com', '2022-07-17');

That adds two new customers in one go.

UPDATE: changing existing data

Data changes. UPDATE lets you modify rows that are already in the table.

Basic UPDATE

John Doe has a new email address:

UPDATE customers 
SET email = 'john.doe@newdomain.com' 
WHERE name = 'John Doe';

That updates John Doe’s email in the customers table.

Updating multiple columns

You can change several columns at once. If John Doe also changed his name to John Smith:

UPDATE customers 
SET email = 'john.smith@newdomain.com', name = 'John Smith' 
WHERE name = 'John Doe';

Both name and email are updated for that row.

DELETE: removing data

DELETE removes rows from a table.

Basic DELETE

Remove a customer from customers:

DELETE FROM customers WHERE name = 'John Smith';

That deletes the record for John Smith.

DELETE with caution

Be careful with DELETE. Without a WHERE clause, it removes every row in the table.

A few practical tips

1. Be precise with WHERE: vague WHERE clauses on UPDATE or DELETE can change or remove more rows than you intended.
2. Back up before bulk changes: before large updates or deletions, make sure you have a recent backup.
3. Test on a copy first: run DELETE and UPDATE on a dev or staging database before touching production.

Wrapping up

INSERT, UPDATE, and DELETE are how you keep a database current. They are straightforward to use, but worth treating with respect – a missing WHERE clause on DELETE can ruin your afternoon.