Menu
Use the RETURNING clause. The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM , can be computed. The new (post-update) values of the table's columns are used.
The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.
UPDATE operations with subqueries that reference the same table object are supported only if all of the following conditions are true: The subquery either returns a single row, or else has no correlated column references. The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax.
You want the OUTPUT clause
UPDATE Items SET Clicks = Clicks + 1
OUTPUT INSERTED.Name
WHERE Id = @Id
Accesses table only once :
DECLARE @Name varchar(MAX);
UPDATE Items SET Clicks = Clicks + 1 , @Name = Name WHERE Id = @Id;
SELECT @Name;
If you're using SQL Server 2005 onwards, the OUTPUT clause is ideal for this
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With