How to Insert, Update, Delete and Alter Data in SQL

A new table starts out empty. Four commands handle the rest of its life: INSERT adds rows, UPDATE edits them, DELETE removes them, and ALTER changes the table’s structure.

Working with Rows

Here is a cafe menu that goes through all three row commands. Pay attention to the WHERE clauses, because they decide which rows get touched.

CREATE TABLE menu_items (
    item_id    SERIAL PRIMARY KEY,
    item_name  VARCHAR(80) NOT NULL,
    category   VARCHAR(30),
    price      NUMERIC(6,2)
);

INSERT INTO menu_items (item_name, category, price)
VALUES
    ('Cappuccino',   'Coffee', 180.00),
    ('Masala Chai',  'Tea',     90.00),
    ('Veg Sandwich', 'Snacks', 150.00);

UPDATE menu_items SET price = 200.00 WHERE item_name = 'Cappuccino';

DELETE FROM menu_items WHERE category = 'Snacks';

SELECT * FROM menu_items;

Output:

Code Explanation:

• INSERT lists the columns first, then one set of values per row. item_id is skipped because SERIAL fills it in.
• UPDATE raises the cappuccino price, and WHERE keeps the change to that one row.
• DELETE removes every row in the Snacks category, which is just the sandwich here.
• The final SELECT shows what is left. The sandwich’s ID 3 is gone for good.

Without a WHERE clause, UPDATE and DELETE act on every row in the table. Before running either on real data, write the same condition as a SELECT first and check which rows come back.

Changing the Table Structure with ALTER

When the table itself needs to change, use ALTER TABLE. For example, ALTER TABLE menu_items ADD COLUMN calories INTEGER; adds a new column, and existing rows get NULL in it until you fill it.

The same command can rename a column with RENAME COLUMN, remove one with DROP COLUMN, or change a data type with ALTER COLUMN ... TYPE.

DELETE, TRUNCATE and DROP

These three all remove things, but they work at different levels.

TRUNCATE and DROP are hard to undo, so check the table name twice before using them.