获取我在 Sql Server 中更新的行的 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1610509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting the Id of a row I updated in Sql Server
提问by Bob The Janitor
I'm trying to return the Id of a row I update in sql
我正在尝试返回我在 sql 中更新的行的 ID
UPDATE ITS2_UserNames
SET AupIp = @AupIp
WHERE @Customer_ID = TCID AND @Handle_ID = ID
SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, SCOPE_IDENTITY() AS ID
and I keep getting Null for the ID, how can I get this?
并且我的 ID 一直为 Null，我怎样才能得到这个？
回答by Remus Rusanu
The @@identityand scope_identity()will hand you the identity of a newrow, ie. after an insert. After your update, the identity of the row is... @Customer_ID or @Handle_Id? If it is a different field, you should use the OUTPUT clause to return the ID of the updated row:
在@@identity和scope_identity()会交给你一个身份新行，即。插入后。更新后，行的标识是...@Customer_ID 还是@Handle_Id？如果是不同的字段，则应使用 OUTPUT 子句返回更新行的 ID：
UPDATE ITS2_UserNames
SET AupIp = @AupIp
OUTPUT INSERTED.PrimaryKeyID
WHERE @Customer_ID = TCID AND @Handle_ID = ID
回答by Samuel Pereira
I think what @pauloya tried to say is:
我认为@paoloya 想说的是：
if you will update a table then you have a WHEREclause, so if you use that same where clause on a select with an INTO #tempTableyou have all rows affected by your UPDATE.
如果你要更新一个表，那么你有一个WHERE子句，所以如果你在选择上使用相同的 where 子句INTO #tempTable，你的UPDATE.
So you can go:
所以你可以去：
SELECT
userName.ID
INTO #temp
FROM ITS2_UserNames AS userNames
WHERE @Customer_ID = TCID AND @Handle_ID = ID
then you update
然后你更新
UPDATE ITS2_UserNames
SET AupIp = @AupIp
WHERE @Customer_ID = TCID AND @Handle_ID = ID
finally you can return all IDs affected by your update
最后，您可以返回受更新影响的所有 ID
SELECT * FROM #temp
You can do this with OUTPUTbut you will have to declare a variable table like
你可以这样做，OUTPUT但你必须声明一个变量表，比如
DECLARE @tempTable TABLE ( ID INT );
and then you use OUTPUT
然后你使用 OUTPUT
UPDATE ITS2_UserNames
SET AupIp = @AupIp
OUTPUT INSERTED.ID
INTO @tempTable
WHERE @Customer_ID = TCID AND @Handle_ID = ID
回答by Harmeet Singh Bhamra
Wanted to mention here that if you want to take affected row's primary key in variable then you can use OUTPUT clause which can put this in a table variable. Execute below statements for example...
想在这里提到的是，如果您想在变量中获取受影响行的主键，那么您可以使用 OUTPUT 子句，该子句可以将其放入表变量中。执行以下语句，例如...
CREATE TABLE ItemTable(ID INT IDENTITY(1,1),Model varchar(500) NULL, Color VARCHAR(50) null)
CREATE TABLE ItemTable(ID INT IDENTITY(1,1),Model varchar(500) NULL, Color VARCHAR(50) null)
INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Yellow')
INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Yellow')
INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Red')
INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Red')
INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Pink')
INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Pink')
DECLARE @LastUpdateID TABLE(ItemID INT null)
声明@LastUpdateID 表（ItemID INT null）
UPDATE ItemTable SET model = 'abc' OUTPUT INSERTED.ID INTO @LastUpdateID WHERE Color = 'Red'
UPDATE ItemTable SET model = 'abc' OUTPUT INSERTED.ID INTO @LastUpdateID WHERE Color = 'Red'
SELECT ItemID FROM @LastUpdateID
从@LastUpdateID 中选择项目ID
回答by pauloya
If you want to find out the records that match that update you should do a select with it
如果您想找出与该更新匹配的记录，您应该使用它进行选择
Select IdColumn
From ITS2_UserNames
WHERE @Customer_ID = TCID AND @Handle_ID = ID
