Insert, Update and Delete

While SQL's query language (DQL) allows retrieving data, the data manipulation language (DML) facilitates insertion, modification and deletion of records in database tables. Mastering these statements helps surface data capture and evolution capabilities.

INSERT Statement

INSERT appends a new record to a table.

Basic syntax:

INSERT INTO table (column1, column2)
VALUES (value1, value2);

This inserts one row with the given column values.

Suppose you have a table named books with columns book_id and title.

The SQL command to insert a new row into this table would be:

INSERT INTO books (book_id, title)
VALUES (1, 'To Kill a Mockingbird');

Alternate syntax supports inserting multiple rows:

INSERT INTO table_name (column_list)
VALUES (row1_values),
       (row2_values);

Suppose you have a table named employees with columns employee_id, name, and department.

The SQL command to insert two new rows into this table would look like this:

INSERT INTO employees (employee_id, name, department)
VALUES (123, 'John Doe', 'Marketing'),
       (456, 'Jane Smith', 'Finance');

Useful for loading batches of data from application processing.

Key capabilities include:

• Explicitly specifying columns inserted
• Inserting multiple rows in one statement
• Querying from another table to COPY data

For example, populate a history table with current data:

INSERT INTO customers_archive
SELECT * FROM customers;

UPDATE Statement

UPDATE modifies existing records.

UPDATE table
SET column = value, other_column = other_value
WHERE condition;

Updates rows meeting the condition by setting columns to given values.

Suppose you have a table named employees with columns employee_id, name, department, and salary.

The SQL command to update the department and salary for a specific employee might look like this:

UPDATE employees
SET department = 'IT', salary = 75000
WHERE employee_id = 123;

Best practices:

• Examine affected rows first without updating
• Use WHERE conditions to limit rows updated
• Validate restrictions before making changes

DELETE Statement

As the name suggests, DELETE removes records:

DELETE FROM table
WHERE condition;

Rows matching the condition are deleted if no restrictions apply.

Suppose you have a table named customers with various columns, including customer_id.

The SQL command to delete a customer with a specific customer_id would be:

DELETE FROM customers
WHERE customer_id = 101;

Similar care should be taken like UPDATE:

• Check affected rows first
• Use WHERE to restrict deletion
• Validate based on relational rules
• Consider archiving data before delete

Learning controlled DML execution takes your SQL skills to the next level for maintaining database integrity over time. Practice safe data manipulation through personalized exercises.

Please note that our test editor doesn't support Insert, Update and Delete queries.👇