什么是通过SQL更新单个记录并获取更新记录的id的最佳方法？ （爪哇/ MSSQL） [英] Whats the best way to update a single record via SQL and obtain the id of the record that was updated? (Java/MSSQL)

问题描述

我知道我可以更新这样的单个记录 - 但是如何才能访问已更新的记录的ID？ （我正在使用MSSQL，所以我不能使用Oracles RowId）

I know I can update a single record like this - but then how to I get access to the id of the record that was updated? (I'm using MSSQL so I can't use Oracles RowId)

update myTable
set myCol = 'foo'
where itemId in (select top 1 itemId from myTable )

如果我正在执行插入我可以使用getGeneratedKeys获取id字段值，但我认为没有相应的更新？

If I was peforming an Insert I could use getGeneratedKeys to get the id field value, but I don't think there is an equivalent for an update?

我知道我可以使用可滚动的结果集来做我想做的事情

I know I can use a scrollable resultset to do what I want

ie

stmt = conn.prepareStatement("select top 1 myCol, itemId from myTable", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = stmt.executeQuery();
if(resultSet.first()){
    resultSet.updateString(1, "foo");
    resultSet.updateRow();
    String theItemId = resultSet.getString(1)
}
resultSet.close();

但是我担心性能，因为测试显示负载下的锁定超时，我想知道是否有更好/更简单的方式？

but I'm concerned about performance as testing shows lock timeouts under load and I was wondering if there was a better/simpler way?

- 编辑：
刚刚完成此问题...
当我们迁移到MSSQL2005时，我们将升级我们的代码使用Rich的答案。
在当前版本中，我们使用了锁提示：（UPDLOCK ROWLOCK READPAST）来缓解原始代码显示的性能问题。

-- Just to finalise this issue... When we migrate to MSSQL2005 we will upgrade our code to use Rich's answer. In the current release we have used the lock hints: (UPDLOCK ROWLOCK READPAST) to mitigate the performance problems our original code showed.

推荐答案

此示例在MSSQL 2005中运行良好...

This example works really well in MSSQL 2005...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[TEST_TABLE]
GO

CREATE TABLE [dbo].[TEST_TABLE](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_TEST_TABLE] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


-- An insert which will return the identity
INSERT INTO [dbo].[TEST_TABLE] ([name]) 
OUTPUT inserted.id
VALUES('Test 1')

-- Another insert which will return the identity
INSERT INTO [dbo].[TEST_TABLE] ([name]) 
OUTPUT inserted.id
VALUES('Test 2')

-- Now an update which will return the identity
UPDATE [dbo].[TEST_TABLE]
SET [name] = 'Updated Test 1'
OUTPUT inserted.id
WHERE [name] = 'Test 1'

SELECT id, [name] FROM [dbo].[TEST_TABLE]

更具体地说是你的查询......

And more specifically to your query...

update myTable
set myCol = 'foo'
output inserted.itemid
where itemId in (select top 1 itemId from myTable )

这篇关于什么是通过SQL更新单个记录并获取更新记录的id的最佳方法？ （爪哇/ MSSQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！