如果行不存在,则需要插入一行并更新行,我已经找到了MySQL的解决方案:
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
姓名="A",年龄=19
但我找不到MSSQL的类似方法。
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
姓名="A",年龄=19
但我找不到MSSQL的类似方法。
INSERT
+UPDATE
)。如果它不存在,则更新不会更新任何内容,如果已经存在,则插入不会插入任何内容:UPDATE T SET
name = 'A',
age = 19
FROM
[table] AS T
WHERE
T.id = 1
INSERT INTO [table] (
id,
name,
age)
SELECT
id = 1,
name = 'A',
age = 19
WHERE
NOT EXISTS (SELECT 'not yet loaded' FROM [table] AS T WHERE T.id = 1)
或者使用MERGE
:
;WITH ValuesToMerge AS
(
SELECT
id = 1,
name = 'A',
age = 19
)
MERGE INTO
[table] AS T
USING
ValuesToMerge AS V ON (T.id = V.id)
WHEN NOT MATCHED BY TARGET THEN
INSERT (
id,
name,
age)
VALUES (
V.id,
V.name,
V.age)
WHEN MATCHED THEN
UPDATE SET
name = V.name,
age = V.name;
我更喜欢检查@@ROWCOUNT
。这是一种更紧凑的解决方案。
UPDATE table set name = 'A', age = 19 WHERE id = 1;
IF @@ROWCOUNT = 0
INSERT INTO table (id, name, age) VALUES(1, "A", 19);