Updating Product Prices

You are tasked with managing an e-commerce platform's product inventory. A common operation is to adjust the prices of products based on various factors, such as sales, promotions, or cost changes. This challenge requires you to implement a solution to efficiently update the prices of specific products in your database.

Problem Description

You need to write a SQL UPDATE statement that modifies the price column for a set of products in a Products table. The update should only apply to products that meet certain criteria.

What needs to be achieved:

• Update the price of specific products in the Products table.

Key requirements:

• The update should be targeted, meaning only certain rows should be modified.
• The new price should be applied based on a given condition.

Expected behavior:

• Rows in the Products table that satisfy the specified WHERE clause conditions should have their price updated to the new value.
• Rows that do not satisfy the conditions should remain unchanged.

Important edge cases to consider:

• What happens if no products match the update criteria? The statement should execute without error and make no changes.
• What if the Products table is empty? Similar to the above, no changes should occur.

Examples

Example 1: Input: Products table:

Update operation: Increase the price of all products in the 'Electronics' category by 10%.

Output: Products table after update:

Explanation: The prices for Laptop, Keyboard, and Mouse were updated because they belong to the 'Electronics' category. The Desk Lamp and Office Chair prices remained the same.

Example 2: Input: Products table (same as Example 1 before update):

Update operation: Set the price of the product with product_id 4 to 45.

Output: Products table after update:

Explanation: Only the price for the Desk Lamp (product_id 4) was updated to 45.

Example 3: No matching records Input: Products table (same as Example 1 before update):

Update operation: Decrease the price of all products in the 'Books' category by 5%.

Output: Products table after update (no changes):

Explanation: Since no products have the category 'Books', the UPDATE statement had no effect.

Constraints

• The Products table will have at least the columns product_id (integer), name (string), price (numeric/decimal), and category (string).
• The price column will always contain a non-negative value.
• The product_id column is the primary key and is unique.
• The UPDATE statement should be efficient and complete its execution within a reasonable time for tables with up to 100,000 rows.

Notes

• Focus on constructing the correct UPDATE statement.
• Consider how to express the update logic using the SET and WHERE clauses of an SQL UPDATE statement.
• For calculations like percentage increases, remember to perform the arithmetic correctly (e.g., price * 1.10 for a 10% increase).