I want to use CASE statement to update some records in SQL Server 2005

Specifying update targets with "WHERE" and "CASE"

Precision is key in updating records. The WHERE clause should only target rows relevant to your update. Within the CASE statement, specify the conditions under which to apply your values. Here's a classic illustration where we precisely bump the salaries for IT employees:

UPDATE Employees
SET Salary = CASE
               WHEN Department = 'IT' THEN Salary * 1.10
               ELSE Salary -- Other departments: Better luck next time
             END
WHERE Department = 'IT'; -- Only IT folks are getting a raise today


In this pattern, the ELSE clause safeguard intactness of unrelated rows, avoiding unintended repercussions.

Tackling multiple conditions

When processing multiple values, the power tag-team of IN operator and CASE statement got your back. As an example, let's end of line some of our specific products:

UPDATE Products
SET Status = CASE
               WHEN ProductID IN (1, 2, 3) THEN 'Discontinued'
               ELSE Status -- For other products, "the show must go on"
             END
WHERE ProductID IN (1, 2, 3);


Complex updates involve **JOIN**s across multiple tables; we all love a good challenge, don't we? Here go the flashy moves:

UPDATE a
SET a.field = CASE
                WHEN condition THEN value
                ELSE a.field -- If it ain't broke...
              END
FROM TableA a
INNER JOIN TableB b ON a.joinKey = b.joinKey -- It's a match!
WHERE someCondition; -- You shall not pass (unless...)


Be the Merlin of Data Integrity

Our Magic, as wizards of SQL Server 2005, is bound by ancient vows to protect Data Integrity. Here are some of the sacred spells:

• Thunderbolt of Duplicates: Cast this spell (or use temporary tables) to isolate the demon duplicates before the grand update ritual.
• Charm of Performance: Minimize strain on the sacred shrine of the database. Conjuring an UPDATE on large datasets can drain its life force.
• Mirror of Consolidation: Engage GROUP BY and spectral spirits like MIN or similar functions to merge crowding records.
• Guardian of Legacy: The spirit of SQL Server 2005 may behave differently from younger spirits in handling updates. Always respect the aged.

Here's a caution: If your CASE is getting jumpy and considering scanning every record in sizable tables, appease it with soothing words or coffee breaks.

Mastering strategies for advanced updates

Next in our magical tour are some arcane techniques to deck your updates with all the bells and whistles:

Precise Incremental Updates

Increment updates precisely: the database gods favor targeted gag—ahem, targeted changes.

UPDATE Accounts
SET Balance = CASE
                WHEN Date < '20230101' THEN Balance * 1.05
                ELSE Balance -- Keep calm and carry (the balance) on
              END
WHERE Date < '20230101' AND Balance < 5000; -- It's a bird.. No, it's a where clause!


Conditionally Royal Joins

Add a touch of royalty by combining the power of JOIN with CASE when the condition stretches its long limbs across multiple tables:

UPDATE p
SET p.Price = CASE
                WHEN c.DiscountRate > 0 THEN p.Price * (1 - c.DiscountRate)
                ELSE p.Price -- Full price, no dicount? Oh, the horror!
              END
FROM Products p
INNER JOIN Categories c ON p.CategoryID = c.CategoryID -- Matchy matchy
WHERE c.DiscountDate = GETDATE(); -- Sorry customers, it's not your discount day


Safeguard against the Shadows

The ELSE clause is a crafty fox in your CASE; left unsupervised, it might play hokey pokey with your data:

-- The magic cloak only touches 'Active' records
UPDATE Orders
SET Status = CASE
               WHEN Quantity > 100 THEN 'Bulk Order'
               ELSE Status -- Invisible hand keeps status quo
             END
WHERE Status = 'Active';
