用于两次更新和搜索查询的高效 SQL 查询

发布于 2024-08-19 13:20:30 字数 223 浏览 3 评论 0原文

我有一个这样的查询：

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

在读取行数据并使用它之后，我想更新检索到的行并更改其中的一列（在另一个事务中）。

但正如你在这里看到的，我搜索了该行两次。有什么方法可以让我记住该行并进行更新而无需再次搜索。

谢谢。

原文

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 any way that I keep remember the row and do the update without searching again.

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ゞ花落谁相伴 2024-08-26 13:20:30

在第一个查询中，您检索了 id。在第二个查询中，使用它来查找要更新的行，而不是使用日期：

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

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

回复收藏 0 原文

伏妖词 2024-08-26 13:20:30

我知道它已经过时了，但您也可以对光标进行定位更新
例如

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

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

回复收藏 0 原文

御弟哥哥 2024-08-26 13:20:30

--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.

这应该可以做到

--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

回复收藏 0 原文