SQL Data Manipulation: INSERT, UPDATE, and DELETE

Covers SQL DML commands for inserting, updating, and deleting data with syntax examples, including important caveats about WHERE clauses.

2 min read

SQL’s Data Manipulation Language (DML) is used to add, modify, and delete data within a database.

1. Inserting Data (INSERT)

The INSERT statement adds new rows (records) to a table.

Inserting Values for All Columns

When inserting values for all columns in a table, you can omit the column names and specify values in the VALUES clause. The order of values must match the column order in the table definition.

INSERT INTO table_name
VALUES (value1, value2, ...);

Example: Add a new product to the products table.

INSERT INTO products
VALUES (101, 'Laptop', 1200.00, 'Electronics', 50);

Inserting Values for Specific Columns

When inserting values for specific columns only, specify the column names after INSERT INTO. Unspecified columns will be filled with their default values or NULL.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example: Add a product specifying only the product ID, name, and price.

INSERT INTO products (product_id, product_name, price)
VALUES (102, 'Mouse', 25.00);

2. Updating Data (UPDATE)

The UPDATE statement modifies values of existing records in a table.

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;
  • SET: Specifies the columns to update and their new values.
  • WHERE: Specifies the condition to identify records to update. Omitting the WHERE clause will update ALL records in the table, so extreme caution is required.

Example: Update the price of the product with product_id 101 in the products table.

UPDATE products
SET price = 1150.00
WHERE product_id = 101;

You can also update based on existing column values:

UPDATE products
SET stock = stock * 0.9; -- Reduce all product stock by 10%

3. Deleting Data (DELETE)

The DELETE statement removes records from a table.

DELETE FROM table_name
WHERE condition;
  • WHERE: Specifies the condition to identify records to delete. Omitting the WHERE clause will delete ALL records in the table, so extreme caution is required.

Example: Delete the product with product_id 102 from the products table.

DELETE FROM products
WHERE product_id = 102;

Delete all records (keeping the table structure):

DELETE FROM products;

Note: The TRUNCATE TABLE command also deletes all records from a table, but it works differently from DELETE internally. TRUNCATE TABLE is a DDL command that is usually faster than DELETE, but it may not be rollable back since it is not recorded in the transaction log.

Mastering these DML commands enables efficient data management in databases.