Question
Answer and Explanation
In SQL, there isn't a direct equivalent of a switch statement like you might find in programming languages such as JavaScript or C++. Instead, SQL uses the CASE expression to handle conditional logic within queries, including UPDATE statements. The CASE expression allows you to specify different update values based on different conditions.
Here's how you can use the CASE expression in an SQL UPDATE query:
1. Basic CASE Expression in an UPDATE:
- The basic form of a CASE expression in an UPDATE query involves evaluating conditions and applying updates based on those conditions. Here’s an example:
UPDATE Employees
SET Salary = CASE
WHEN Department = 'Sales' THEN Salary 1.10
WHEN Department = 'Marketing' THEN Salary 1.05
WHEN Department = 'Engineering' THEN Salary 1.15
ELSE Salary
END;
In this example, the query checks each employee's 'Department' and updates the 'Salary' field accordingly. If an employee does not match any of the specified departments, the salary will be left unchanged (using ELSE Salary).
2. Using CASE for Different Column Updates:
- You can also use CASE to update different columns based on a condition. For instance:
UPDATE Products
SET
Price = CASE
WHEN Category = 'Electronics' THEN Price 0.9
ELSE Price
END,
Stock = CASE
WHEN Category = 'Books' THEN Stock + 100
ELSE Stock
END;
This updates both the Price and Stock columns, applying different logic to each based on the Product's Category. Products in the 'Electronics' category get a 10% price discount, and for 'Books' category, stock is increased by 100.
3. Using a Searched CASE Expression:
- For more complex conditions involving multiple checks, you can use a searched CASE statement. This is similar to multiple IF ... ELSE IF ... conditions:
UPDATE Orders
SET Status = CASE
WHEN OrderDate < '2023-01-01' AND Status = 'Pending' THEN 'Cancelled'
WHEN TotalAmount > 1000 AND Status = 'Pending' THEN 'Approved'
ELSE Status
END;
Here, the update query checks both the 'OrderDate' and 'TotalAmount' along with the current 'Status' to determine the new status. If 'OrderDate' is before '2023-01-01' and the status is pending then the status becomes 'Cancelled', and if 'TotalAmount' is over 1000 and the status is pending, then status changes to 'Approved'
4. Benefits of Using CASE:
- CASE expressions are versatile, allowing you to handle many different conditional logic within an SQL query. They keep the logic within the database layer, reducing the need for multiple separate queries and enhancing the performance. The CASE expression ensures only a single database trip for all data modifications.
In conclusion, While SQL lacks a direct switch statement, the CASE expression provides a robust and versatile way to handle conditional logic within an UPDATE query, allowing you to apply different updates based on varying conditions.