Inserting, Updating, and Deleting Data (DML)

Ready to make changes to your database? Let's explore Data Manipulation Language (DML) commands in SQL. These commands let you **insert** new data, **update** existing data, and **delete** data from your tables.

What's Inside

1. INSERT Statement
2. UPDATE Statement
3. DELETE Statement
4. Transaction Control

1. INSERT Statement

The INSERT statement lets you add new rows to a table.

Syntax

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

Example: Let's add a new customer to the Customers table.

INSERT INTO Customers (CustomerName, ContactName, Country)
VALUES ('Tech Corp', 'John Doe', 'USA');

2. UPDATE Statement

The UPDATE statement lets you change existing records in a table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Update the contact name for a customer.

UPDATE Customers
SET ContactName = 'Jane Smith'
WHERE CustomerID = 1;

3. DELETE Statement

The DELETE statement lets you remove records from a table.

Syntax

DELETE FROM table_name
WHERE condition;

Example: Delete a customer record.

DELETE FROM Customers
WHERE CustomerID = 1;

Watch out: If you skip the WHERE clause, **all** records in the table will be deleted. Oops!

4. Transaction Control

Transactions help you run a bunch of SQL statements as a single unit. This ensures data stays **safe** and **consistent**.

The key commands are:

• START TRANSACTION: Begin a new transaction.
• COMMIT: Save all changes made during the transaction.
• ROLLBACK: Undo all changes made during the transaction.

Example: Let's transfer money between two accounts. If something goes wrong, we'll roll back the changes.

START TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 2;

COMMIT;

If any step fails, you can use ROLLBACK to undo everything. Keeps things tidy!