SELECT and UPDATE Data

• Rechana Rajan

  SSCertifiable

  Points: 7717

  More actions

  March 3, 2019 at 11:43 pm

  #382854

  Hi All,

  We have a table which have high data insert with a status flag column , I want to select data having status=1 and then update the status to 2. The table get inserted very frequently and now what my procedure does is like below.
  create procedure gettext1
  as begin
  set nocount on;
    UPDATE [dbo].[communication1] set status1=2 where status1=1

  SELECT text1
    FROM [dbo].[communication1] with (nolock) where status1=2

  UPDATE [dbo].[communication1] set status1=3 where status1=2
  end

  How can I make this more efficient as I feel this is not the right approach.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 4, 2019 at 4:45 am

  #2023415

  Rechana Rajan - Sunday, March 3, 2019 11:43 PM

  Hi All,

  We have a table which have high data insert with a status flag column , I want to select data having status=1 and then update the status to 2. The table get inserted very frequently and now what my procedure does is like below.
  create procedure gettext1
  as begin
  set nocount on;
    UPDATE [dbo].[communication1] set status1=2 where status1=1

  SELECT text1
    FROM [dbo].[communication1] with (nolock) where status1=2

  UPDATE [dbo].[communication1] set status1=3 where status1=2
  end

  How can I make this more efficient as I feel this is not the right approach.

  Use the OUTPUT clause and change status1 from 1 to 3:

  UPDATE [dbo].[communication1] SET status1 = 3
  OUTPUT DELETED.text1
  WHERE status1 = 1

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden