SQL UPDATE

Basic UPDATE

UPDATE customers
SET city = 'Los Angeles'
WHERE id = 3;

Changes the city of the customer with ID 3 to Los Angeles.

Without WHERE Clause

UPDATE products
SET price = 0;

This sets all product prices to 0 — use with caution!

Updating Multiple Columns

UPDATE users
SET name = 'John', email = 'john@example.com'
WHERE id = 1;

Updates both name and email in one query.

Using Expressions

UPDATE accounts
SET balance = balance - 100
WHERE id = 10;

Subtracts 100 from the balance of account 10.

JOIN with UPDATE

In some SQL dialects (like MySQL), you can use a JOIN inside an UPDATE statement to modify rows in one table based on matching rows in another table. This is especially useful when you need to update data conditionally using related information from another table.

UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET orders.status = 'priority'
WHERE customers.vip = 1;

In this example:

orders is the table being updated.
JOIN customers links each order to its customer using the customer_id foreign key.
SET orders.status = 'priority' updates the status field in orders.
WHERE customers.vip = 1 ensures that only orders from VIP customers are updated.

This approach helps maintain data consistency across related tables and is commonly used in real-world applications like promotions, access control, and user preference updates.

Best Practices

Test updates on sample data first
Use transactions when updating critical rows
Log affected row counts for traceability

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic

Menu