Updating Records with UPDATE

SQL's UPDATE statement changes values in rows that already exist in a table.

Imagine a students table:

| student_id | student_name | city | age |
|---:|---|---|---:|
| 1 | Asha | Lucknow | 20 |
| 2 | Rahul | Noida | 21 |
| 3 | Neha | Delhi | 22 |

Suppose Rahul moves from Noida to Lucknow. You do not need to delete his row and insert another one. You can update the city value.

### Basic syntax

```sql
UPDATE table_name
SET column_name = new_value
WHERE condition;
```

Understand each part:

- UPDATE identifies the table.
- SET specifies which column values to change.
- WHERE identifies the rows to update.

### Your first UPDATE

```sql
UPDATE students
SET city = 'Lucknow'
WHERE student_id = 2;
```

Only the row with student_id 2 is targeted.

### Verify the result

```sql
SELECT student_id, student_name, city
FROM students
WHERE student_id = 2;
```

Expected result, assuming the original sample data:

| student_id | student_name | city |
|---:|---|---|
| 2 | Rahul | Lucknow |

**Important:** UPDATE changes stored data. Always understand which rows your WHERE condition matches before running an important update.

Use this table to practice the statements in this lesson.

### Create the table

```sql
CREATE TABLE employees (
employee_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(60) NOT NULL,
salary NUMERIC(10, 2) NOT NULL CHECK (salary >= 0),
city VARCHAR(80),
active BOOLEAN NOT NULL DEFAULT TRUE
);
```

### Insert sample records

```sql
INSERT INTO employees
(employee_name, department, salary, city, active)
VALUES
('Asha', 'Engineering', 50000.00, 'Lucknow', TRUE),
('Rahul', 'Sales', 40000.00, 'Noida', TRUE),
('Neha', 'Engineering', 60000.00, 'Delhi', TRUE),
('Kabir', 'Support', 35000.00, NULL, TRUE),
('Meera', 'Sales', 45000.00, 'Lucknow', FALSE);
```

### Inspect the data

```sql
SELECT employee_id, employee_name, department, salary, city, active
FROM employees
ORDER BY employee_id;
```

The identity values are generated by PostgreSQL. This sample assumes a fresh table and no other inserts.

### Why use NUMERIC for salary?

NUMERIC supports exact decimal arithmetic within its declared precision and scale. It is commonly suitable for monetary amounts where binary floating-point approximation is undesirable.

The CHECK constraint prevents negative salaries. An UPDATE that tries to set salary below zero will fail.

Let's change Rahul's salary.

```sql
UPDATE employees
SET salary = 42000.00
WHERE employee_name = 'Rahul';
```

This targets rows whose employee_name equals Rahul.

A primary-key condition is generally a clearer choice when you know the employee ID:

```sql
UPDATE employees
SET salary = 42000.00
WHERE employee_id = 2;
```

The exact ID assumes the sample table was freshly created and populated as shown.

### Verify the change

```sql
SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id = 2;
```

Expected result under that assumption:

| employee_id | employee_name | salary |
|---:|---|---:|
| 2 | Rahul | 42000.00 |

### A crucial distinction

```sql
UPDATE employees
SET salary = 42000.00;
```

This statement has no WHERE clause. It targets every row in employees.

Use an unrestricted UPDATE only when changing all rows is genuinely intended.

You can change more than one column in the same UPDATE statement.

### Example

```sql
UPDATE employees
SET
city = 'Lucknow',
department = 'Engineering'
WHERE employee_id = 2;
```

Both assignments apply to the targeted row.

### Verify

```sql
SELECT employee_id, employee_name, department, city
FROM employees
WHERE employee_id = 2;
```

### Remember

- Separate assignments with commas.
- Do not put a comma immediately before WHERE.
- Use the correct data type for each value.
- Include a condition that identifies the intended rows.

### Incorrect syntax

```sql
UPDATE employees
SET city = 'Lucknow',
WHERE employee_id = 2;
```

The trailing comma is invalid SQL.

### Correct syntax

```sql
UPDATE employees
SET city = 'Lucknow'
WHERE employee_id = 2;
```

WHERE is one of the most important parts of a safe UPDATE.

### Example: Update a department

```sql
UPDATE employees
SET department = 'Customer Support'
WHERE department = 'Support';
```

Every row whose department is Support is targeted.

### What if you omit WHERE?

```sql
UPDATE employees
SET department = 'Customer Support';
```

Every row in employees is targeted, regardless of its previous department.

This can accidentally overwrite data across the entire table.

### Safe workflow before updating

First, preview the target rows:

```sql
SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'Support';
```

Then run the corresponding UPDATE:

```sql
UPDATE employees
SET department = 'Customer Support'
WHERE department = 'Support';
```

### Important limitation

A preview SELECT and a later UPDATE are separate statements. In a concurrent database, data may change between them. For high-impact updates, consider a transaction, appropriate locking or isolation, and a condition that identifies the intended records.

**Rule:** Never remove WHERE merely to make an UPDATE easier to write. If every row should change, make that intention explicit and verify the expected scope.

The SET clause can use expressions, including existing column values.

### Example 1: Increase every salary by 5%

```sql
UPDATE employees
SET salary = salary * 1.05;
```

This increases every employee's salary by 5%. It is an all-row update because it has no WHERE clause.

### Example 2: Increase only Engineering salaries

```sql
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering';
```

Only employees in Engineering are targeted.

### Example 3: Give a fixed increase

```sql
UPDATE employees
SET salary = salary + 2000
WHERE employee_id = 2;
```

If Rahul's salary is 42000.00, the result becomes 44000.00.

### Example 4: Calculate a new value from two columns

Suppose a products table has price and discount_percent columns.

```sql
UPDATE products
SET sale_price = price * (1 - discount_percent / 100.0)
WHERE product_id = 10;
```

This example assumes the table has these columns and the discount is stored as a percentage such as 15 for 15%.

### Avoid accidental repeated increases

Running a 5% salary increase twice applies two increases:

- First update: salary × 1.05
- Second update: the already-increased salary × 1.05

The combined increase is 10.25%, not exactly 10%.

For one-time business adjustments, make sure the operation is not unintentionally repeated.

WHERE supports comparison and logical operators, so you can target rows precisely.

### Use a comparison operator

```sql
UPDATE employees
SET active = FALSE
WHERE salary < 36000;
```

This targets employees with salaries below 36000.

### Combine conditions with AND

```sql
UPDATE employees
SET city = 'Lucknow'
WHERE department = 'Engineering'
AND salary >= 50000;
```

Both conditions must evaluate to TRUE for a row to qualify.

### Combine conditions with OR

```sql
UPDATE employees
SET active = FALSE
WHERE department = 'Support'
OR salary < 36000;
```

A row qualifies when at least one condition evaluates to TRUE.

### Use IS NULL

```sql
UPDATE employees
SET city = 'Not Provided'
WHERE city IS NULL;
```

This targets rows where city is NULL.

Do not use city = NULL. Ordinary comparisons with NULL evaluate to UNKNOWN rather than TRUE.

### Preview a condition first

```sql
SELECT employee_id, employee_name, salary, city
FROM employees
WHERE city IS NULL;
```

The SELECT helps you inspect the intended targets before applying the change.

NULL needs special attention in update expressions.

### Set a column to NULL

```sql
UPDATE employees
SET city = NULL
WHERE employee_id = 2;
```

This works because city permits NULL.

If the column were declared NOT NULL, the update would fail.

### Use COALESCE to fill missing values

```sql
UPDATE employees
SET city = COALESCE(city, 'Unknown')
WHERE city IS NULL;
```

COALESCE returns the first non-NULL argument. Since the WHERE condition selects only rows with NULL city, those rows receive Unknown.

### Why this expression alone is different

```sql
UPDATE employees
SET city = COALESCE(city, 'Unknown');
```

This targets every row. It preserves existing non-NULL city values but still executes an UPDATE for all rows.

### NULL arithmetic

```sql
UPDATE employees
SET salary = salary + 1000
WHERE city = 'Lucknow';
```

If salary were NULL, adding 1000 would produce NULL. In our practice table, salary is NOT NULL, so that case is prevented by the schema.

PostgreSQL supports RETURNING to display values from rows affected by an UPDATE.

### Return updated rows

```sql
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 2
RETURNING employee_id, employee_name, salary;
```

The result contains the updated row's values.

### Return several updated rows

```sql
UPDATE employees
SET active = FALSE
WHERE department = 'Support'
RETURNING employee_id, employee_name, active;
```

Every row updated by the statement can appear in the returned result.

### Why is RETURNING useful?

- It confirms which rows were affected.
- It lets an application obtain updated values without a separate SELECT.
- It can return generated or calculated values stored by the database.
- It helps with auditing and API responses when used appropriately.

### Important details

- RETURNING does not commit a transaction.
- If the UPDATE fails, the statement does not return a successful result.
- The number of returned rows is the number of rows affected by the statement, not necessarily the number you expected.
- Avoid returning sensitive columns unnecessarily.

PostgreSQL supports UPDATE ... FROM, which lets an UPDATE use data from another table.

Imagine a department_targets table:

```sql
CREATE TABLE department_targets (
department VARCHAR(60) PRIMARY KEY,
salary_increase NUMERIC(5, 2) NOT NULL
CHECK (salary_increase >= 0)
);
```

Insert example targets:

```sql
INSERT INTO department_targets (department, salary_increase)
VALUES
('Engineering', 5.00),
('Sales', 3.00);
```

The percentage values are stored as 5.00 for 5%, and 3.00 for 3%.

### Apply the department-specific increase

```sql
UPDATE employees AS e
SET salary = e.salary * (1 + t.salary_increase / 100.0)
FROM department_targets AS t
WHERE e.department = t.department
RETURNING e.employee_name, e.department, e.salary;
```

The join condition connects each employee to the target for their department.

### Critical rule: Match each target row unambiguously

For an UPDATE ... FROM query, avoid source data that produces multiple matching rows for a single target row. PostgreSQL does not provide a reliable business rule for choosing among multiple source matches.

In this example, department_targets.department is a primary key, so each department has at most one target percentage.

### Use aliases clearly

- e refers to employees.
- t refers to department_targets.

Table aliases make multi-table statements easier to read.

### When is this useful?

- Updating product prices from a pricing table.
- Updating account status from a review table.
- Applying department-specific settings.
- Synchronizing a table from a staging dataset.

For high-impact changes, a transaction can give you an opportunity to inspect the results before committing.

### Example: Review before committing

```sql
BEGIN;

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering'
RETURNING employee_id, employee_name, salary;

-- Review the returned rows and any other required checks.
-- If the result is correct:
COMMIT;
```

If you discover a problem before committing, use:

```sql
ROLLBACK;
```

ROLLBACK discards the transaction's uncommitted changes.

### What transactions do not guarantee

A transaction does not automatically mean that the WHERE condition is correct. You must still verify the intended target rows and follow the application's concurrency requirements.

### Useful safety workflow

1. Identify the target rows.
2. Preview them with SELECT.
3. Use a sufficiently specific WHERE condition.
4. For important changes, execute within an appropriate transaction.
5. Inspect RETURNING output or another verification query.
6. Commit only when the result is correct.
7. Roll back if the result is wrong and the transaction remains open.

### Be careful with external side effects

A database rollback does not undo an email already sent, an external API call, or another side effect outside that transaction.

Constraints continue to protect the table when rows are updated.

### CHECK constraint example

Our employees table has:

```sql
CHECK (salary >= 0)
```

The following update fails:

```sql
UPDATE employees
SET salary = -500
WHERE employee_id = 2;
```

The new salary violates the constraint.

### NOT NULL constraint example

```sql
UPDATE employees
SET employee_name = NULL
WHERE employee_id = 2;
```

This fails because employee_name is NOT NULL.

### UNIQUE and foreign-key constraints

If an update changes a unique key to a value already used by another row, the relevant UNIQUE constraint can reject it.

If an update changes a foreign-key column to a value that does not reference an allowed parent row, the foreign-key constraint can reject it.

### Correct response to a constraint error

- Inspect the violated constraint.
- Check whether the proposed value is correct.
- Check whether the business rule is correct.
- Correct the data or the intended operation.
- Do not casually disable integrity constraints to force the update through.

Use the employees table created earlier. Run exercises in a practice database, not against important production data.

### Exercise 1: Update a city

Change Rahul's city to Lucknow using his employee_id.

### Exercise 2: Update multiple columns

Change Neha's city to Noida and her department to Data Engineering.

### Exercise 3: Increase one salary

Increase Rahul's salary by 2000.

### Exercise 4: Give a percentage increase

Increase salaries in Engineering by 5%.

### Exercise 5: Target low salaries

Set active to FALSE for employees earning less than 36000.

### Exercise 6: Fill NULL cities

Set missing city values to Unknown without changing existing non-NULL cities.

### Exercise 7: Use RETURNING

Deactivate employees in Sales and return their IDs, names, and active status.

### Exercise 8: Diagnose the missing WHERE

Explain what happens when you execute UPDATE employees SET active = FALSE.

### Exercise 9: Diagnose a constraint violation

Explain why setting salary to -100 fails.

### Exercise 10: Use a transaction

Write an UPDATE inside a transaction and roll it back.

### Exercise 11: Update from another table

Use department_targets to apply the salary percentage associated with each employee's department.

### Exercise 12: Prevent SQL injection

Explain how to safely update an employee's city using a user-supplied value in a Node.js PostgreSQL application.

### Solution 1: Update a city

```sql
UPDATE employees
SET city = 'Lucknow'
WHERE employee_id = 2;
```

This assumes the sample table was freshly populated and Rahul has ID 2.

### Solution 2: Update multiple columns

```sql
UPDATE employees
SET
city = 'Noida',
department = 'Data Engineering'
WHERE employee_name = 'Neha';
```

In a real database, prefer a unique identifier when one is available.

### Solution 3: Increase one salary

```sql
UPDATE employees
SET salary = salary + 2000
WHERE employee_id = 2;
```

### Solution 4: Give a percentage increase

```sql
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering';
```

### Solution 5: Target low salaries

```sql
UPDATE employees
SET active = FALSE
WHERE salary < 36000;
```

### Solution 6: Fill NULL cities

```sql
UPDATE employees
SET city = 'Unknown'
WHERE city IS NULL;
```

### Solution 7: Use RETURNING

```sql
UPDATE employees
SET active = FALSE
WHERE department = 'Sales'
RETURNING employee_id, employee_name, active;
```

### Solution 8: Diagnose the missing WHERE

Every row in employees is targeted, and active is set to FALSE for every row.

### Solution 9: Diagnose a constraint violation

The CHECK constraint requires salary >= 0. A salary of -100 violates it, so PostgreSQL rejects the statement.

### Solution 10: Use a transaction

```sql
BEGIN;

UPDATE employees
SET city = 'Temporary City'
WHERE employee_id = 2;

ROLLBACK;
```

The update is rolled back if the transaction has not been committed and no other relevant behavior changes the outcome.

### Solution 11: Update from another table

```sql
UPDATE employees AS e
SET salary = e.salary * (1 + t.salary_increase / 100.0)
FROM department_targets AS t
WHERE e.department = t.department
RETURNING e.employee_name, e.department, e.salary;
```

This assumes department_targets contains at most one row for each department.

### Solution 12: Parameterized update

```javascript
const result = await pool.query(
"UPDATE employees SET city = $1 WHERE employee_id = $2 RETURNING employee_id, city",
[newCity, employeeId]
);
```

The values are passed separately from the SQL text, helping protect against SQL injection.

Choose the best answer for each question.

### 1. What is UPDATE in SQL?

UPDATE is a DML statement that changes values in existing rows.

### 2. What is the purpose of SET?

SET specifies the columns to change and the expressions that calculate their new values.

### 3. Why is WHERE important in UPDATE?

WHERE identifies the rows to change. Without it, every row in the target table is targeted.

### 4. Can one UPDATE change multiple columns?

Yes. Separate assignments in SET with commas.

### 5. Can an UPDATE use the current column value?

Yes. For example, SET salary = salary + 1000 uses the current salary to calculate the new value.

### 6. How do you increase a salary by 5%?

Use SET salary = salary * 1.05.

### 7. How do you update only rows meeting two conditions?

Use AND in the WHERE clause.

### 8. How do you check for NULL in an UPDATE condition?

Use IS NULL or IS NOT NULL, not ordinary equality with NULL.

### 9. What does PostgreSQL RETURNING provide?

It returns values from rows affected by the UPDATE statement.

### 10. Does RETURNING commit a transaction?

No. It returns row values but does not commit an explicit transaction.

### 11. What happens if an UPDATE violates a CHECK constraint?

PostgreSQL normally rejects the statement, and the invalid change is not accepted.

### 12. What is UPDATE ... FROM?

It is PostgreSQL syntax for updating target rows using data from another source table in the FROM clause.

### 13. Why must UPDATE ... FROM matches be unambiguous?

If one target row matches multiple source rows, the chosen source row is not reliably defined. Design the join so each target row has at most one source match.

### 14. How can you undo an UPDATE?

If the change is still uncommitted in a transaction, ROLLBACK can discard it. After commit, recovery generally requires a compensating update or another recovery mechanism.

### 15. Why preview rows before a high-impact UPDATE?

A SELECT can help confirm which rows match the intended condition before modifying them.

### 16. What is a calculated UPDATE?

It assigns values computed from expressions, such as salary = salary * 1.05.

### 17. Why can repeated UPDATE statements be dangerous?

A repeated expression may apply again to already-modified data, producing an unintended cumulative change.

### 18. What is the difference between UPDATE and INSERT?

UPDATE modifies existing rows. INSERT creates new rows.

### 19. How do you safely update data from a Node.js application?

Use parameterized queries, validate inputs, apply appropriate WHERE conditions, and handle errors.

### 20. What is a mass update?

An update affecting many or all rows. It may be intentional, but its scope and impact should be verified before execution.

## Summary

- UPDATE changes existing rows.
- SET defines the new values.
- WHERE identifies the rows to change.
- Without WHERE, every row in the target table is targeted.
- Calculated expressions can use existing column values.
- IS NULL is the correct way to test for NULL.
- RETURNING displays changed values in PostgreSQL.
- UPDATE ... FROM can use another table as a source.
- Constraints can reject invalid updates.
- Transactions support committing or rolling back uncommitted changes.
- Parameterized queries help protect application code.
- Repeated updates can compound changes.

## Final checklist

- [ ] Write an UPDATE statement with SET.
- [ ] Update a single row using a key.
- [ ] Update more than one column.
- [ ] Explain the effect of omitting WHERE.
- [ ] Write a calculated update.
- [ ] Filter rows using AND and OR.
- [ ] Use IS NULL correctly.
- [ ] Use RETURNING to inspect changes.
- [ ] Explain UPDATE ... FROM.
- [ ] Describe how constraints affect updates.
- [ ] Use a transaction and ROLLBACK in a practice database.
- [ ] Explain why parameterized queries are safer.

**Next:** Continue to the next lesson in the Modifying Data chapter.

Avoid these common errors when updating records.