Efficient SQL query for two update and search queries

Question

I have a query like this:

SELECT TOP 1 ID, DATA, OTHERINF FROM MYTABLE WHERE DATE = @DATE

and after reading the row data and using it I want to update that retrieved row and change one of it's columns (in another transaction).

But as you see here i searched for that row twice. Is there anyway that I keep remember the row and do the update without searching again.

Thank you.

Answer 1

--Note, need to setup proper data types
DECLARE @Id INT
DECLARE @Data VARCHAR(MAX)
DECLARE @OtherInf VARCHAR(max)

SELECT TOP 1 @id = Id, @Data = Data, @OtherInf = OtherInf
FROM MyTable
WHERE Date = @Date

--Now you can do what you need, using the info above.

This should do it

Answer 2

In the first query you retrieved the id. In the second query use that to find the row to update instead of using the date:

UPDATE MYTABLE
SET DATA = 'FooBar'
WHERE ID = 200

Answer 3

You can combine the UPDATE with the SELECT into one statement, but not across two transactions. Therefore, if you need to update the value in another transaction than you select it (the reason for this is unclear to me), you need two statements.

Answer 4

I presume that the DATE column isn't indexed (if not, why not?) Your best bet, then, is to make sure you retrieve the primary key (isn't that ID?) and then use that as your condition in the update.

Answer 5

I know its out of vogue but you can also do positioned updates on cursors e.g.

use Northwind
GO



DECLARE EMP_CURSOR CURSOR 
FOR SELECT TOP 1 EmployeeID, LastName FROM EMPLOYEES WHERE  HireDate = '1994-11-15'
FOR UPDATE OF LastName

OPEN EMP_CURSOR

FETCH NEXT FROM EMP_CURSOR 

UPDATE EMPLOYEES
SET LastName = LastName + CAST(DatePart(ms,GetDate()) as char(3))
WHERE CURRENT OF EMP_CURSOR


CLOSE EMP_CURSOR

DEALLOCATE  EMP_CURSOR