UPDATE Query

The UPDATE Function

The UPDATE command is used to modify existing data in a table. Without a WHERE clause, all rows will be affected.

Basic Syntax

UPDATE table_name
SET column1 = grades1, column2 = grades2
WHERE kondisi;

Updating a Single Column

A simple example of changing an employee's address:

UPDATE EMPLOYEE
SET ADDRESS = '100 Main St, Chicago'
WHERE ID_EMPLOYEE = 1;

This command only changes the address of the employee with ID 1.

Updating Multiple Columns

We can change several columns at once in a single UPDATE command by separating each update with a comma.

UPDATE EMPLOYEE
SET ADDRESS = '50 Park Ave, New York',
    PHONE = '081234567890'
WHERE ID_EMPLOYEE = 3;

The query above changes the address and phone number of the employee with ID 3.

UPDATE with Logical Operators

Combine UPDATE with the AND / OR operators learned previously.

UPDATE EMPLOYEE
SET SALARY = SALARY + 500000
WHERE DEPARTMENT = 'IT' AND STATUS = 'Active';

All active employees in the IT department will receive a salary increase of 500,000.

UPDATE with Expressions

SET also supports arithmetic expressions and functions.

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.10
WHERE DEPARTMENT = 'HR';

HR employees receive a 10% salary increase.

UPDATE with a Subquery

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.05
WHERE DEPARTMENT = (SELECT DEPARTMENT_NAME FROM DEPARTMENT WHERE CODE = 'IT');

The Danger of UPDATE without WHERE

WARNING: Running UPDATE without a WHERE clause will modify every row in the table.

-- DANGER! Do not run this in production!
UPDATE EMPLOYEE
SET SALARY = 0;

The statement above will set the salary of all employees to 0. Always make sure a WHERE clause is present before running UPDATE.

Complete Scenario Example

The EMPLOYEE table before the update:

UPDATE EMPLOYEE
SET ADDRESS = '15 Lake Shore Dr, San Francisco',
    SALARY = 8000000
WHERE ID = 1;

-- EMPLOYEE after the update:
-- | 1 | Mike | 15 Lake Shore Dr, San Francisco | 8000000 |
-- | 2 | John | New St. 5 | 4500000 |

Safety Tips

1. Always test your UPDATE query with SELECT first
2. Use transactions (BEGIN, ROLLBACK, COMMIT) for important changes
3. Always include a WHERE clause unless you are sure you want to change all the data

Now we understand how to update data. The next chapter covers how to delete data using DELETE.