arvb.net

SQL Update

2 min read

The UPDATE statement in SQL is used to modify existing rows in a table. It can update a single row, multiple rows, or even use data from another table to perform the update. This is a core part of data manipulation, enabling you to change stored data based on conditions or joins.

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name: The table to be updated.
  • SET: Specifies the columns to be modified and their new values.
  • WHERE: Filters which rows should be updated. Omitting WHERE updates all rows.

Example 1: Updating a Single Row

Update an employee’s salary by increasing it by 10%:

UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = 123;

Explanation:

  • Only the row with employee_id equal to 123 is updated.
  • The salary is multiplied by 1.10, reflecting a 10% raise.

Example 2: Updating Multiple Rows

Set the status of all employees in the “Sales” department to ‘active’:

UPDATE employees
SET status = 'active'
WHERE department = 'Sales';

Explanation:

  • All rows where department is “Sales” will have their status column updated to 'active'.

Example 3: Updating Data from Another Table

Method A: Using a Subquery

Update the price of products based on a multiplier from another table:

UPDATE products
SET price = price * (
    SELECT multiplier 
    FROM pricing_factors 
    WHERE pricing_factors.product_id = products.product_id
)
WHERE EXISTS (
    SELECT 1 
    FROM pricing_factors 
    WHERE pricing_factors.product_id = products.product_id
);

Explanation:

  • For each product, the price is multiplied by a multiplier retrieved from pricing_factors.
  • The WHERE EXISTS clause ensures that only products with a corresponding entry in pricing_factors are updated.

Method B: Using a JOIN in PostgreSQL

Update an orders table using discount information from a discounts table:

UPDATE orders
SET amount = orders.amount * discounts.discount_multiplier
FROM discounts
WHERE orders.customer_id = discounts.customer_id
  AND discounts.valid = true;

Explanation:

  • This query joins orders with discounts on customer_id.
  • It updates the amount by applying the discount_multiplier for valid discounts.
  • The FROM clause (supported in PostgreSQL) is used to join tables directly in an update.

Graph View

Backlinks