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

Question

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

User · Answer

Perhaps something more like this   declare  UpdateTime datetime  set  UpdateTime   getutcdate    update Table1 set AlertDate    UpdateTime where AlertDate is null  select ID from Table1 where AlertDate    UpdateTime

User · Answer

Edit  my bad  you wanted the select to show results after the update  not update from a select   Have you tried a sub-select   update mytable set mydate   sysdate  where mydate in  select mydate from mytable where mydate is null

User · Answer

Edit  my bad  you wanted the select to show results after the update  not update from a select   Have you tried a sub-select   update mytable set mydate   sysdate  where mydate in  select mydate from mytable where mydate is null

User · Answer

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

User · Answer

in SQL 2008 a new TSQL statement  MERGE  is introduced which performs insert  update  or delete operations on a target table based on the results of a join with a source table  You can synchronize two tables by inserting  updating  or deleting rows in one table based on differences found in the other table   http   blogs msdn com ajaiman archive 2008 06 25 tsql-merge-statement-sql-2008 aspx  http   msdn microsoft com en-us library bb510625 aspx

User · Answer

if it s inside the transaction  the database locking system will take care of concurrency issues  of course  if you use one  the mssql default is that it uses lock  so it states if you don t override that

User · Answer

Many years later     The accepted answer of using the OUTPUT clause is good  I had to dig up the actual syntax  so here it is   DECLARE  UpdatedIDs table  ID int  UPDATE      Table1  SET      AlertDate   getutcdate    OUTPUT     inserted Id INTO      UpdatedIDs WHERE      AlertDate IS NULL    ADDED SEP 14  2015    Can I use a scalar variable instead of a table variable   one may ask    Sorry  but no you can t  You ll have to SELECT  SomeID   ID from  UpdatedIDs if you need a single ID

User · Answer

One way to handle this is to do it in a transaction  and make your SELECT query take an update lock on the rows selected until the transaction completes  BEGIN TRAN  SELECT Id FROM Table1 WITH  UPDLOCK  WHERE AlertDate IS NULL   UPDATE Table1 SET AlertDate   getutcdate    WHERE AlertDate IS NULL   COMMIT TRAN   This eliminates the possibility that a concurrent client updates the rows selected in the moment between your SELECT and your UPDATE  When you commit the transaction  the update locks will be released  Another way to handle this is to declare a cursor for your SELECT with the FOR UPDATE option   Then UPDATE WHERE CURRENT OF CURSOR   The following is not tested  but should give you the basic idea  DECLARE cur1 CURSOR FOR   SELECT AlertDate FROM Table1    WHERE AlertDate IS NULL   FOR UPDATE   DECLARE  UpdateTime DATETIME  SET  UpdateTime   GETUTCDATE    OPEN cur1   FETCH NEXT FROM cur1   WHILE   FETCH STATUS   0 BEGIN    UPDATE Table1    SET AlertDate    UpdateTime  --set value   WHERE CURRENT OF cur1     FETCH NEXT FROM cur1     END

User · Answer

if it s inside the transaction  the database locking system will take care of concurrency issues  of course  if you use one  the mssql default is that it uses lock  so it states if you don t override that

User · Answer

in SQL 2008 a new TSQL statement  MERGE  is introduced which performs insert  update  or delete operations on a target table based on the results of a join with a source table  You can synchronize two tables by inserting  updating  or deleting rows in one table based on differences found in the other table   http   blogs msdn com ajaiman archive 2008 06 25 tsql-merge-statement-sql-2008 aspx  http   msdn microsoft com en-us library bb510625 aspx

User · Answer

if it s inside the transaction  the database locking system will take care of concurrency issues  of course  if you use one  the mssql default is that it uses lock  so it states if you don t override that

User · Answer

in SQL 2008 a new TSQL statement  MERGE  is introduced which performs insert  update  or delete operations on a target table based on the results of a join with a source table  You can synchronize two tables by inserting  updating  or deleting rows in one table based on differences found in the other table   http   blogs msdn com ajaiman archive 2008 06 25 tsql-merge-statement-sql-2008 aspx  http   msdn microsoft com en-us library bb510625 aspx

User · Answer

I have faced the same issue  I have to update the credit amount  and have to get modified time  along with credit details from DB  It is basically   SYNCHRONOUSLY ATOMICALLY perform  UPDATE then GET  in MYSQL  I have tried many options and found one that solved my issue   1  OPTION 1  SELECT FOR UPDATE  This is maintaining the lock till update  SYNC from GET to UPDATE   but i need lock after update till the GET   2  OPTION 2  Stored procedure   Stored procedure will not execute synchronously like redis lua  So there also we need sync code to perform that   3  OPTION 3  Transaction  I have used JPA entityManager like below  thought that before commit no one can update  and before commit i will get the updated object along with modified time  from DB   But i didn t get the latest object  Only commit i got the latest       try           entityManager getTransaction   begin              entityManager persist object           int upsert   entityManager createNativeQuery           update com bill Credit c set c balance   c balance -  1           where c accountId    2 and c balance  gt    1   executeUpdate                    c balance  gt     for limit check         Credit newCredit   entityManager find Credit class   id            entityManager refresh newCredit     SHOULD GET LATEST BUT NOT         entityManager getTransaction   commit          finally                entityManager unwrap Session class  close             4  OPTION 4 LOCK solved the issue  so before update i acquired the lock  then after GET i have released the lock   private Object getLock final EntityManager entityManager  final String Id        entityManager getTransaction   begin        Object obj acquire   entityManager createNativeQuery  SELECT GET LOCK      Id       10    getSingleResult        entityManager getTransaction   commit        return obj acquire      private Object releaseLock final EntityManager entityManager  final String Id        entityManager getTransaction   begin        Object obj release   entityManager createNativeQuery  SELECT RELEASE LOCK      Id         getSingleResult        entityManager getTransaction   commit        return obj release

User · Answer

Perhaps something more like this   declare  UpdateTime datetime  set  UpdateTime   getutcdate    update Table1 set AlertDate    UpdateTime where AlertDate is null  select ID from Table1 where AlertDate    UpdateTime

User · Answer

if it s inside the transaction  the database locking system will take care of concurrency issues  of course  if you use one  the mssql default is that it uses lock  so it states if you don t override that

User · Answer

in SQL 2008 a new TSQL statement  MERGE  is introduced which performs insert  update  or delete operations on a target table based on the results of a join with a source table  You can synchronize two tables by inserting  updating  or deleting rows in one table based on differences found in the other table   http   blogs msdn com ajaiman archive 2008 06 25 tsql-merge-statement-sql-2008 aspx  http   msdn microsoft com en-us library bb510625 aspx

User · Answer

Perhaps something more like this   declare  UpdateTime datetime  set  UpdateTime   getutcdate    update Table1 set AlertDate    UpdateTime where AlertDate is null  select ID from Table1 where AlertDate    UpdateTime

User · Answer

One way to handle this is to do it in a transaction  and make your SELECT query take an update lock on the rows selected until the transaction completes  BEGIN TRAN  SELECT Id FROM Table1 WITH  UPDLOCK  WHERE AlertDate IS NULL   UPDATE Table1 SET AlertDate   getutcdate    WHERE AlertDate IS NULL   COMMIT TRAN   This eliminates the possibility that a concurrent client updates the rows selected in the moment between your SELECT and your UPDATE  When you commit the transaction  the update locks will be released  Another way to handle this is to declare a cursor for your SELECT with the FOR UPDATE option   Then UPDATE WHERE CURRENT OF CURSOR   The following is not tested  but should give you the basic idea  DECLARE cur1 CURSOR FOR   SELECT AlertDate FROM Table1    WHERE AlertDate IS NULL   FOR UPDATE   DECLARE  UpdateTime DATETIME  SET  UpdateTime   GETUTCDATE    OPEN cur1   FETCH NEXT FROM cur1   WHILE   FETCH STATUS   0 BEGIN    UPDATE Table1    SET AlertDate    UpdateTime  --set value   WHERE CURRENT OF cur1     FETCH NEXT FROM cur1     END

User · Answer

One way to handle this is to do it in a transaction  and make your SELECT query take an update lock on the rows selected until the transaction completes  BEGIN TRAN  SELECT Id FROM Table1 WITH  UPDLOCK  WHERE AlertDate IS NULL   UPDATE Table1 SET AlertDate   getutcdate    WHERE AlertDate IS NULL   COMMIT TRAN   This eliminates the possibility that a concurrent client updates the rows selected in the moment between your SELECT and your UPDATE  When you commit the transaction  the update locks will be released  Another way to handle this is to declare a cursor for your SELECT with the FOR UPDATE option   Then UPDATE WHERE CURRENT OF CURSOR   The following is not tested  but should give you the basic idea  DECLARE cur1 CURSOR FOR   SELECT AlertDate FROM Table1    WHERE AlertDate IS NULL   FOR UPDATE   DECLARE  UpdateTime DATETIME  SET  UpdateTime   GETUTCDATE    OPEN cur1   FETCH NEXT FROM cur1   WHILE   FETCH STATUS   0 BEGIN    UPDATE Table1    SET AlertDate    UpdateTime  --set value   WHERE CURRENT OF cur1     FETCH NEXT FROM cur1     END

User · Answer

Edit  my bad  you wanted the select to show results after the update  not update from a select   Have you tried a sub-select   update mytable set mydate   sysdate  where mydate in  select mydate from mytable where mydate is null

User · Answer

It d be easier to do your UPDATE first and then run  SELECT ID FROM INSERTED    Take a look at SQL Tips for more info and examples

User · Answer

One way to handle this is to do it in a transaction  and make your SELECT query take an update lock on the rows selected until the transaction completes  BEGIN TRAN  SELECT Id FROM Table1 WITH  UPDLOCK  WHERE AlertDate IS NULL   UPDATE Table1 SET AlertDate   getutcdate    WHERE AlertDate IS NULL   COMMIT TRAN   This eliminates the possibility that a concurrent client updates the rows selected in the moment between your SELECT and your UPDATE  When you commit the transaction  the update locks will be released  Another way to handle this is to declare a cursor for your SELECT with the FOR UPDATE option   Then UPDATE WHERE CURRENT OF CURSOR   The following is not tested  but should give you the basic idea  DECLARE cur1 CURSOR FOR   SELECT AlertDate FROM Table1    WHERE AlertDate IS NULL   FOR UPDATE   DECLARE  UpdateTime DATETIME  SET  UpdateTime   GETUTCDATE    OPEN cur1   FETCH NEXT FROM cur1   WHILE   FETCH STATUS   0 BEGIN    UPDATE Table1    SET AlertDate    UpdateTime  --set value   WHERE CURRENT OF cur1     FETCH NEXT FROM cur1     END

User · Answer

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

User · Answer

It d be easier to do your UPDATE first and then run  SELECT ID FROM INSERTED    Take a look at SQL Tips for more info and examples

User · Answer

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

User · Answer

It d be easier to do your UPDATE first and then run  SELECT ID FROM INSERTED    Take a look at SQL Tips for more info and examples

User · Answer

Many years later     The accepted answer of using the OUTPUT clause is good  I had to dig up the actual syntax  so here it is   DECLARE  UpdatedIDs table  ID int  UPDATE      Table1  SET      AlertDate   getutcdate    OUTPUT     inserted Id INTO      UpdatedIDs WHERE      AlertDate IS NULL    ADDED SEP 14  2015    Can I use a scalar variable instead of a table variable   one may ask    Sorry  but no you can t  You ll have to SELECT  SomeID   ID from  UpdatedIDs if you need a single ID

User · Answer

It d be easier to do your UPDATE first and then run  SELECT ID FROM INSERTED    Take a look at SQL Tips for more info and examples

User · Answer

Edit  my bad  you wanted the select to show results after the update  not update from a select   Have you tried a sub-select   update mytable set mydate   sysdate  where mydate in  select mydate from mytable where mydate is null

User · Answer

I have faced the same issue  I have to update the credit amount  and have to get modified time  along with credit details from DB  It is basically   SYNCHRONOUSLY ATOMICALLY perform  UPDATE then GET  in MYSQL  I have tried many options and found one that solved my issue   1  OPTION 1  SELECT FOR UPDATE  This is maintaining the lock till update  SYNC from GET to UPDATE   but i need lock after update till the GET   2  OPTION 2  Stored procedure   Stored procedure will not execute synchronously like redis lua  So there also we need sync code to perform that   3  OPTION 3  Transaction  I have used JPA entityManager like below  thought that before commit no one can update  and before commit i will get the updated object along with modified time  from DB   But i didn t get the latest object  Only commit i got the latest       try           entityManager getTransaction   begin              entityManager persist object           int upsert   entityManager createNativeQuery           update com bill Credit c set c balance   c balance -  1           where c accountId    2 and c balance  gt    1   executeUpdate                    c balance  gt     for limit check         Credit newCredit   entityManager find Credit class   id            entityManager refresh newCredit     SHOULD GET LATEST BUT NOT         entityManager getTransaction   commit          finally                entityManager unwrap Session class  close             4  OPTION 4 LOCK solved the issue  so before update i acquired the lock  then after GET i have released the lock   private Object getLock final EntityManager entityManager  final String Id        entityManager getTransaction   begin        Object obj acquire   entityManager createNativeQuery  SELECT GET LOCK      Id       10    getSingleResult        entityManager getTransaction   commit        return obj acquire      private Object releaseLock final EntityManager entityManager  final String Id        entityManager getTransaction   begin        Object obj release   entityManager createNativeQuery  SELECT RELEASE LOCK      Id         getSingleResult        entityManager getTransaction   commit        return obj release

User · Answer

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

User · Answer

Perhaps something more like this   declare  UpdateTime datetime  set  UpdateTime   getutcdate    update Table1 set AlertDate    UpdateTime where AlertDate is null  select ID from Table1 where AlertDate    UpdateTime

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008