Modifying data in SQL typically involves changing existing records in a database. There are a few main SQL statements used for modifying data: UPDATE, DELETE, and sometimes INSERT (if adding new rows). Here’s a detailed breakdown:
1. UPDATE
Used to modify existing records in a table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example:
Suppose you have a table employees:
| id | name | salary |
|---|---|---|
| 1 | Alice | 5000 |
| 2 | Bob | 4500 |
If you want to give Bob a raise:
UPDATE employees
SET salary = 5000
WHERE name = 'Bob';
Important:
- The
WHEREclause is crucial. Without it, all rows will be updated. - You can use expressions, e.g.,
SET salary = salary * 1.1.
2. DELETE
Used to remove rows from a table.
Syntax:
DELETE FROM table_name
WHERE condition;
Example:
DELETE FROM employees
WHERE name = 'Alice';
Important:
- Without a
WHEREclause, all rows in the table are deleted. - Always double-check the condition before running.
3. INSERT
Technically not modifying existing rows, but used to add new data.
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Example:
INSERT INTO employees (name, salary)
VALUES ('Charlie', 4700);
4. Tips for Modifying Data Safely
- Always backup your data before running
UPDATEorDELETE. - Use transactions if your database supports it:
BEGIN TRANSACTION;
UPDATE employees SET salary = 5500 WHERE name = 'Bob';
COMMIT; -- or ROLLBACK if something goes wrong
- Test with SELECT first:
SELECT * FROM employees WHERE name = 'Bob';
This ensures your WHERE condition affects only the intended rows.