Menu
I'd like to update a set of rows based on a simple criteria and get the list of PKs that were changed. I thought I could just do something like this but am worried about possible concurrency problems:
SELECT Id FROM Table1 WHERE AlertDate IS NULL; UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL; If that is wrapped in a transaction are there any concurrency issues that can occur? Or is there a better way to do this?
395
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.
There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
Syntax: UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2 ---- WHERE condition; Here table_name is the name of the table, column_name is the column whose value you want to update, new_value is the updated value, WHERE is used to filter for specific data.
Consider looking at the OUTPUT clause:
USE AdventureWorks2012; GO DECLARE @MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime); UPDATE TOP (10) HumanResources.Employee SET VacationHours = VacationHours * 1.25, ModifiedDate = GETDATE() OUTPUT inserted.BusinessEntityID, deleted.VacationHours, inserted.VacationHours, inserted.ModifiedDate INTO @MyTableVar; --Display the result set of the table variable. SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate FROM @MyTableVar; GO --Display the result set of the table. SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate FROM HumanResources.Employee; GO 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
