使用 Case 语句插入表或更新现有行 [英] Using Case statement to either Insert into a table or Update an existing row

问题描述

可能的重复:
在 SQL Server 上插入或更新的解决方案
仅在行不存在时插入行

我的标题几乎解释了我想要做什么，但我会更详细地介绍一下.我正在创建一个存储过程，调用它时首先检查该行是否已经存在(通过与两个参数进行比较)，如果存在，它将更新该行中的特定列，如果该行不存在它将在表中插入一个新行.

My title pretty much explains what I'm trying to do, but I'll go into a little more detail. I'm creating a stored procedure when called it first checks to see if the row already exists (by comparing against two parameters) and if it does, it will update a specific column in the row and if the row doesn't exist already it will insert a new row into the table.

BEGIN
SELECT 
(
CASE WHEN [Site] = @site and Plant = @plant
then 
UPDATE [Status]
FROM Server_Status
WHERE [Site] = @site
ELSE
Insert into Server_Status(Name, [Path], [Site], Plant, [Status])
Values (@name, @path, @site, @plant, @status)
end
)
FROM Server_Status 
END

这是我到目前为止所拥有的，但不起作用(显然).有没有比我更懂 SQL 的人有什么建议?

Is what I have so far, but doesn't work (obviously). Does anyone with more SQL knowledge than I have any suggestions?

-J

推荐答案

您可能需要查看 MERGE (Transact-SQL) 语句.

You might want to take ta look at the MERGE (Transact-SQL) statement.

基于目标表执行插入、更新或删除操作与源表的连接结果.例如，您可以通过在一个表中插入、更新或删除行来同步两个表表基于在另一个表中发现的差异.

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

这篇关于使用 Case 语句插入表或更新现有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！