SQL 更新 - 更新选定的行 [英] SQL Update - Updating selected rows

问题描述

我使用的是 SQL Server 2008

I am using SQL Server 2008

我有一个名为 MYTABLE 的表，有两列:ID、STATUS

I have a table named MYTABLE with two columns: ID, STATUS

我想写一个存储过程，返回STATUS为0的记录.但是这个存储过程必须将返回行的STATUS更新为1.我怎样才能在单个查询中执行此选择和更新操作?

I want to write a stored procedure that returns the records whose STATUS is 0. But this stored proc must update the STATUS of returned rows to 1. How can I do this selecting and updating operation in a single query?

推荐答案

update MyTable
set Status = 1
output inserted.*
where Status = 0

如果你想返回更新前表格的样子，你应该使用deleted.*代替.

If you want to return what the table looked like before the update you should use deleted.* instead.

update MyTable
set Status = 1
output deleted.*
where Status = 0

如果您愿意，您当然可以同时使用两者，而不必使用 *.您可以指定您感兴趣的列.

You can of course use both if you like and you don't have to use *. You can specify the columns you are interested in.

update MyTable
set Status = 1
output inserted.ID, inserted.status, deleted.status as OldStatus
where Status = 0

这篇关于SQL 更新 - 更新选定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！