blocks|key|1934129|text|此查询不会返回任何内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1934130|SELECT+id+FROM+@t+WHERE+ID+=+2|code-block|syntax|javascript|1934131|因为where在表中没有ID+=+2的行，所以没有东西可以合并到表中。|1934132|1934133|此外，在MATCHED子句中，您正在更新您正在连接表的字段ID，我认为这是被禁止的。|1934134|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|O|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|P|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|R|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|M|$]]

This query won't return anything:

<pre><code>SELECT id FROM @t WHERE ID = 2
</code></pre>

Because where is no rows in table with ID = 2, so there is nothing to merge into table.

<hr>

Besides, in MATCHED clause you are updating a field ID on which you are joining table, i think, it's forbidden.

blocks|key|1936852|text|对于每个DML操作，您必须提交(标记成功事务的结束)，然后只有您才能看到最新数据|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1936853|例如：|1936854|GO
BEGIN+TRANSACTION;
GO
DELETE+FROM+HumanResources.JobCandidate
++++WHERE+JobCandidateID+=+13;
GO
COMMIT+TRANSACTION;
GO|code-block|syntax|javascript|1936855|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

For each DML operations you have to commit (Marks the end of a successful the transaction)Then only you will be able to see the latest data

For example :

<pre><code>GO
BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
 WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION;
GO
</code></pre>

blocks|key|4348299|text|您的USING部分稍微有点混乱，这就是您想要匹配的内容的位置(尽管在本例中您只使用id)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4348300|declare+@t+table
(
++++id+int,
++++name+varchar(10)
)

insert+into+@t+values(1,'a')

MERGE+INTO+@t+t1
USING+(SELECT+2,+'b')+AS+t2+(id,+name)+ON+(t1.id+=+t2.id)
WHEN+MATCHED+THEN
++++UPDATE+SET+name+=+'d',+id+=+3
WHEN+NOT+MATCHED+THEN
++++INSERT+(id,+name)
++++VALUES+(2,+'b');

select+*+from+@t;|code-block|syntax|javascript|4348301|entityMap^0|2|5|15|2|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

You have your <code>USING</code> part slightly messed up, that's where to put what you want to match against (although in this case you're only using <code>id</code>)

<pre><code>declare @t table
(
 id int,
 name varchar(10)
)

insert into @t values(1,'a')

MERGE INTO @t t1
USING (SELECT 2, 'b') AS t2 (id, name) ON (t1.id = t2.id)
WHEN MATCHED THEN
 UPDATE SET name = 'd', id = 3
WHEN NOT MATCHED THEN
 INSERT (id, name)
 VALUES (2, 'b');

select * from @t;
</code></pre>

blocks|key|4348348|text|正如Mikhail所指出的，USING子句中的查询不包含任何行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4348349|如果要执行upsert，请将新数据放入USING子句：|4348350|MERGE+INTO+@t+t1
USING+(SELECT+2+as+id,+'b'+as+name)+t2+ON+(t1.id+=+t2.id)+--This+no+longer+has+an+artificial+dependency+on+@t
WHEN+MATCHED+THEN
++++UPDATE+SET+name+=+t2.name
WHEN+NOT+MATCHED+THEN
++++INSERT+(id,+name)
++++VALUES+(t2.id,+t2.name);|code-block|syntax|javascript|4348351|entityMap^0|E|5|0|J|5|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

As Mikhail pointed out, your query in the <code>USING</code> clause doesn't contain any rows.

If you want to do an upsert, put the new data into the <code>USING</code> clause:

<pre><code>MERGE INTO @t t1
USING (SELECT 2 as id, 'b' as name) t2 ON (t1.id = t2.id) --This no longer has an artificial dependency on @t
WHEN MATCHED THEN
 UPDATE SET name = t2.name
WHEN NOT MATCHED THEN
 INSERT (id, name)
 VALUES (t2.id, t2.name);
</code></pre>

I am going to use MERGE to insert or update a table depending upon ehether it's exist or not. This is my query,

<pre><code>declare @t table
(
 id int,
 name varchar(10)
)
insert into @t values(1,'a')

MERGE INTO @t t1
USING (SELECT id FROM @t WHERE ID = 2) t2 ON (t1.id = t2.id)
WHEN MATCHED THEN
 UPDATE SET name = 'd', id = 3
WHEN NOT MATCHED THEN
 INSERT (id, name)
 VALUES (2, 'b');

select * from @t;
</code></pre>

The result is,

<pre><code>id name
1 a
</code></pre>

I think it should be,

<pre><code>id name
1 a
2 b
</code></pre>

Converting Traditional IF EXIST UPDATE ELSE INSERT into MERGE is not working?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我将使用MERGE来插入或更新表，这取决于它是否存在。这是我的问题，declare @t table(    id int,    name varchar(10))insert into @t values(1,'a')MERGE INTO @t t1USING (SELECT id FROM @t WHERE ID...

问将传统的IF EXIST UPDATE ELSE INSERT转换为MERGE不起作用吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将传统的IF EXIST UPDATE ELSE INSERT转换为MERGE不起作用吗？EN