UPDATE Command

Overview

The UPDATE command modifies one or more existing rows in a table. It allows you to change values in specific columns for rows that match your criteria.

Syntax

UPDATE <table_name> 
SET <column> = <value> [, <column> = <value>] 
WHERE <condition>;

Examples

Simple Update (Single Column)

Update the salary of a specific user:

UPDATE users SET salary = 55000.00 WHERE name = 'Alice';

Update Multiple Columns

Change multiple fields at once using a comma separator:

UPDATE employees 
SET salary = 65000.00, role = 'Senior Engineer' 
WHERE id = 5;

Conditional Update

Deactivate inactive employees and reset their salary:

UPDATE employees 
SET active = FALSE, salary = 0 
WHERE age > 65 AND role = 'Intern';

Fast Update (O(1) Lookup)

When filtering by Primary Key, MaazDB optimizes the operation:

UPDATE users SET active = TRUE WHERE id = 1;

Update Timestamp

Update a timestamp field to mark modification time:

UPDATE users 
SET last_updated = '2024-01-29 15:30:00' 
WHERE name = 'Bob';

Data Type Compatibility

Values must match the target column's data type:

WHERE Clause Operators

Comparison

Standard operators: =, >, <, >=, <=, !=

UPDATE products SET price = 10.00 WHERE price < 5.00;

Logical

Combine conditions: AND, OR, NOT

UPDATE users SET role = 'admin' WHERE age > 30 AND active = TRUE;

Null Checks

Check for existence: IS NULL, IS NOT NULL

UPDATE users SET status = 'unknown' WHERE phone IS NULL;

Best Practices

1. Test Before Update

Run a SELECT query with the same WHERE clause to verify which rows will be affected.

-- Check first
SELECT * FROM users WHERE age > 65;

-- Then update
UPDATE users SET status = 'retired' WHERE age > 65;

2. Create Backups

Before bulk updates, create a snapshot.

BACKUP 'pre_update_snapshot';
UPDATE users SET salary = salary + 1000;

3. Respect Foreign Keys

Updating a column referenced by a foreign key may fail if the new value doesn't exist in the parent table.

DML Comparison