如果数据已存在，如何将当前标识更新为先前的标识 [英] How can I update the current id as previous id if data already exists

问题描述

我有一个表格，其中RFID列是唯一的，如果用户试图在RFID列中输入重复数据，那么LogID应该更新到之前的LogId



我尝试过：



表：



Logid Name RFID Type

1 Arya AS123 ADD

2 Arya AS123 ADD



这里LogId 2应该更新为1. br />


Thnaks。

I have a table in which RFID column is unique one, if user tried to enter the duplicate data in RFID column then the LogID should update to previous LogId

What I have tried:

Table:

Logid Name RFID Type
1 Arya AS123 ADD
2 Arya AS123 ADD

Here LogId 2 should update as 1.

Thnaks.

推荐答案

对于多个值，我建议MERGE：MERGE（Transact-SQL） - SQL Server Microsoft Docs [ ^ ]但对于单个项目，这并不可行，因为您需要一个临时表来合并。



对于单个项目，尝试UPDATE，如果这不起作用，请执行INSERT：

For multiple values, I'd suggest MERGE: MERGE (Transact-SQL) - SQL Server | Microsoft Docs[^] but for single items, that's not really feasible as you'd need a temporary table to merge from.

For a single item, try an UPDATE, and if that doesn't work, do an INSERT:

Quote：

BEGIN TRAN

UPDATE MyTable SET [Name] = @NAME，Type = @TYPE WHERE RFID = @RFID

IF @@ ROWCOUNT = 0
BEGIN

INSERT INTO MyTableTable（[Name]，RFID，Type）VALUES（@NAME，@ RFID，@ TYPE）

END

COMMIT TRAN

BEGIN TRAN
UPDATE MyTable SET [Name] = @NAME, Type = @TYPE WHERE RFID = @RFID
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO MyTableTable ([Name], RFID, Type) VALUES (@NAME, @RFID, @TYPE)
END
COMMIT TRAN

另一种方法是使用 EXISTS 来检查记录是否存在，然后根据结果执行INSERT或UPDATE。

Another method would be to use EXISTS to check if the record is present, and then do INSERT or UPDATE depending on the results.

IF EXISTS(SELECT 1 FROM MyTable WHERE RFID = @RFID) BEGIN
  UPDATE MyTable SET [Name] = @NAME, [Type] = @TYPE WHERE RFID = @RFID
END; ELSE BEGIN
  INSERT INTO MyTableTable ([Name], RFID, [Type]) VALUES (@NAME, @RFID, @TYPE)
END

选择使用哪种方式取决于您的业务规则，使用等...如果它归结为或者，在SSMS中运行两个查询并选择显示实际执行计划以查看哪个更有效



注意：您会注意到 Name 和 Type 列都包含在方括号中。通常我会避免将它们用作列名，因为它们是保留/保护/特殊字。我也会避免使用你正在使用的任何编程语言中的特殊名称

The way to choose which one to use is going to depend on your business rules, usage, etc... and if it comes down to either or, run both queries in SSMS and choose to Show Actual Execution Plan to see which one is more efficient

NOTE: You will notice that both you Name and Type columns are wrapped in square brackets. Generally I would avoid using these as column names as they are reserved/protected/special words. I also would avoid using "special" names from whatever programming language you are working with as well

这篇关于如果数据已存在，如何将当前标识更新为先前的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！