SQL Server：IF EXISTS;其他 [英] SQL Server: IF EXISTS ; ELSE

问题描述

我有一张桌子A：

ID value
 1  100
 2  101
 2  444
 3  501

另外TableB

ID Code
1
2

现在我想在表A中存在ID = 2时填充表B的col =代码。对于多个值，获取最大值。
else用'123'填充它。现在我使用的是：

Now I want to populate col = code of table B if there exists ID = 2 in tableA. for multiple values , get max value. else populate it with '123'. Now here is what I used:

if exists (select MAX(value) from #A where id = 2)
 BEGIN
 update #B
 set code = (select MAX(value) from #A where id = 2)
 from #A
 END

 ELSE 

 update #B
 set code = 123
 from #B

我确信在BEGIN; END或IF EXIST; ELSE中存在一些问题。
基本上，如果IF部分中的select语句存在，我想绕过else部分，反之亦然。例如，如果IF = part的select语句是：

I am sure there is some problem in BEGIN;END or in IF EXIST;ELSE. Basically I want to by-pass the else part if select statement in IF-part exist and vice- versa. For example if select statement of IF=part is:

(select MAX(value) from #A where id = 4)

它应该只填充123，因为ID = 4不存在！请教育！
提前付款

It should just populate 123, coz ID = 4 do not exist ! Please educate ! Thanks in Advance

推荐答案

编辑

我想补充你的 IF 语句似乎不起作用的原因。当您在聚合上执行 EXISTS 时，它总是 true 。即使 ID 不存在，它也会返回一个值。当然，它是 NULL ，但它返回它。相反，这样做：

I want to add the reason that your IF statement seems to not work. When you do an EXISTS on an aggregate, it's always going to be true. It returns a value even if the ID doesn't exist. Sure, it's NULL, but its returning it. Instead, do this:

if exists(select 1 from table where id = 4)

你将获得的 ELSE 部分IF 声明。

and you'll get to the ELSE portion of your IF statement.

现在，这是一个更好的，基于集合的解决方案：

Now, here's a better, set-based solution:

update b
  set code = isnull(a.value, 123)
from #b b
left join (select id, max(value) from #a group by id) a
  on b.id = a.id
where
  b.id = yourid

这样做的好处是能够在整个表格而不是单个ID上运行。

This has the benefit of being able to run on the entire table rather than individual ids.

这篇关于SQL Server：IF EXISTS;其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！