SQL INSERT, UPDATE and DELETE

SQL INSERT, UPDATE and DELETE are the statements that change data. INSERT INTO adds new rows, UPDATE modifies existing rows, and DELETE removes rows. Together with SELECT they are known as CRUD: create, read, update, delete.

The playground resets on every run, so experiment freely.

INSERT INTO

Always list the columns you are filling. Columns you leave out get their default value or NULL. The INTEGER PRIMARY KEY in SQLite fills itself automatically, like AUTO_INCREMENT in MySQL or IDENTITY in SQL Server.

INSERT INTO Customers (CustomerName, ContactName, City, Country)
VALUES ('Kite Kitchen', 'Hina Malik', 'Lahore', 'Pakistan');

SELECT * FROM Customers WHERE CustomerID > 9;
CustomerID | CustomerName | ContactName | City    | Country
-----------+--------------+-------------+---------+---------
10         | Jade Imports | NULL        | Toronto | Canada
11         | Kite Kitchen | Hina Malik  | Lahore  | Pakistan
(2 rows)

Insert Multiple Rows

Separate several value lists with commas.

INSERT INTO Products (ProductName, Category, Price, Stock)
VALUES ('Black Pepper', 'Spices', 3.5, 150),
       ('Sea Salt', 'Spices', 2.0, 400);

SELECT ProductID, ProductName, Category FROM Products WHERE Category = 'Spices';
ProductID | ProductName  | Category
----------+--------------+---------
9         | Black Pepper | Spices
10        | Sea Salt     | Spices
(2 rows)

UPDATE

UPDATE sets new values for rows that match WHERE. Here honey is restocked and gets a small price rise.

UPDATE Products
SET Stock = 50, Price = Price * 1.1
WHERE ProductID = 8;

SELECT ProductName, ROUND(Price, 2) AS Price, Stock FROM Products WHERE ProductID = 8;
ProductName | Price | Stock
------------+-------+------
Honey       | 12.65 | 50
(1 row)

DELETE

DELETE removes rows that match WHERE.

DELETE FROM Orders
WHERE OrderDate < '2026-02-01';

SELECT COUNT(*) AS remaining_orders FROM Orders;
remaining_orders
----------------
8
(1 row)

Safety Rules

  • UPDATE or DELETE without WHERE changes every row in the table
  • Run a SELECT with the same WHERE first to see which rows will be affected
  • Use transactions (next chapter) so you can roll back mistakes
  • MySQL Workbench has "safe updates" mode that blocks UPDATE/DELETE without a key in WHERE

Frequently asked questions

How do I get the ID of the row I just inserted?

SQLite: last_insert_rowid(). MySQL: LAST_INSERT_ID(). SQL Server: SCOPE_IDENTITY() or OUTPUT. PostgreSQL and SQLite 3.35+: INSERT ... RETURNING id.

What happens if I run DELETE without WHERE?

Every row in the table is deleted. The table itself remains. Unless you are inside a transaction or have a backup, the data is gone.

Can I UPDATE using values from another table?

Yes. Use a subquery in SET or WHERE, or vendor syntax such as UPDATE ... JOIN in MySQL and UPDATE ... FROM in SQL Server, PostgreSQL and SQLite 3.33+.