Is there a way to SELECT and UPDATE rows at the same time?

Transactions: Your safety net for data changes

Feather your database operations nest with transactions — these can safeguard atomicity and maintain data integrity. To use a transaction, initiate one, apply an UPDLOCK to help dodge concurrency issues and proceed:

BEGIN TRANSACTION;

UPDATE MyTable 
SET MyColumn = 'newValue' 
OUTPUT INSERTED.* 
WHERE MyCondition WITH (UPDLOCK);

-- You could play a quick Tetris game now, your rows are safe!
COMMIT TRANSACTION;


This script essentially flags your select rows, preventing any pesky operations from meddling with them until you commit or rollback your transaction.

UPDLOCK and HOLDLOCK duo: An Iron Man suit for concurrency protection

For a tougher line of defense against concurrency issues, try pairing UPDLOCK with HOLDLOCK. This command duo ensures that your selected rows are locked down until the end of the transaction, warding off deadlocks or dirty reads like they're unwanted telemarketers.

BEGIN TRANSACTION;

SELECT * FROM MyTable WITH (UPDLOCK, HOLDLOCK)
WHERE MyCondition;

-- These rows won't budge! They're frozen, like your computer on a Windows update.
UPDATE MyTable
SET MyColumn = 'newValue'
WHERE MyCondition;

COMMIT TRANSACTION;


Power of cursors: The drilldown method for updates

Get needle-precise with your row-specific operations by implementing a cursor. Pair it with UPDATE WHERE CURRENT OF CURSOR for surgical precision when updating rows.

DECLARE MyCursor CURSOR FOR 
SELECT * FROM MyTable WHERE MyCondition;

OPEN MyCursor;
-- Cursor has entered the chat.
FETCH NEXT FROM MyCursor;
-- Time to fetch some data. Might take a while, grab a coffee!

UPDATE MyTable
SET MyColumn = 'newValue'
WHERE CURRENT OF MyCursor;

-- Cursor is tired now.
CLOSE MyCursor;
DEALLOCATE MyCursor;
-- Cursor has left the chat.


This method, though relatively slower, offers fine-tuned control over each update.

Making your updates time travelers

Make your updated rows timeless by timestamping them. Include a timestamp column in your table, updated with the current time, and say hello to audit trails and perfect synchronization.

UPDATE MyTable 
SET MyColumn = 'newValue', 
LastUpdated = GETUTCDATE() 
OUTPUT INSERTED.* 
WHERE MyCondition;


With this simple tweak, every updated row will also sport a fresh timestamp in the OUTPUT.

Getting ID cards for your updated rows

After applying an update, it's easy to capture the IDs of the affected rows by molding from the INSERTED table like clay.

UPDATE MyTable 
SET MyColumn = 'newValue' 
OUTPUT INSERTED.ID 
INTO @ChangedIDs
WHERE MyCondition;


Here, @ChangedIDs is a table variable - acting as a name-badge machine for saved IDs of returned primary keys.

Stored Procedures: Neat packages of reusable logic

Using stored procedures to wrap your SQL operations is pretty neat and keeps your codebase immaculate. Picture it like assembling an IKEA furniture: neatly organized with detailed instruction manuals, reusable with varying parameters.

CREATE PROCEDURE UpdateAndGetRows
    @newValue VARCHAR(255),
    @conditionColumn VARCHAR(255)
AS
BEGIN
    UPDATE MyTable 
    SET MyColumn = @newValue 
    OUTPUT INSERTED.* 
    WHERE MyColumn = @conditionColumn;
END;
