SQL Server：如果存在；别的

发布于 2024-12-04 10:04:30 字数 644 浏览 1 评论 0原文

我有一个表 A：

还有表 B

ID Code
1
2

现在，如果表 A 中存在 ID = 2，我想填充表 B 的 col = 代码。对于多个值，获取最大值。否则用“123”填充它。现在这是我使用的：

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 语句是：

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

它应该只填充 123，因为 ID = 4 不存在！

原文

I have a tableA:

Also TableB

ID Code
1
2

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

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)

It should just populate 123, coz ID = 4 do not exist !

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

若无相欠,怎会相见 2024-12-11 10:04:30

编辑

我想添加您的IF语句似乎不起作用的原因。当您对聚合执行 EXISTS 时，它始终为 true。即使 ID 不存在，它也会返回一个值。当然，它是NULL，但它返回了它。相反，请执行以下操作：

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

您将到达 IF 语句的 ELSE 部分。

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

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 上运行。

EDIT

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)

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

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

回复收藏 0 原文

通知家属抬走 2024-12-11 10:04:30

试试这个：

Update TableB Set
  Code = Coalesce(
    (Select Max(Value)
    From TableA 
    Where Id = b.Id), 123)
From TableB b

Try this:

Update TableB Set
  Code = Coalesce(
    (Select Max(Value)
    From TableA 
    Where Id = b.Id), 123)
From TableB b

回复收藏 0 原文

別甾虛僞 2024-12-11 10:04:30

我知道距离原来的帖子已经有一段时间了，但我喜欢使用 CTE，这对我有用：

WITH cte_table_a
AS
(
    SELECT [id] [id]
    , MAX([value]) [value]
    FROM table_a
    GROUP BY [id]
)
UPDATE table_b
SET table_b.code = CASE WHEN cte_table_a.[value] IS NOT NULL THEN cte_table_a.[value] ELSE 124 END
FROM table_b
LEFT OUTER JOIN  cte_table_a
ON table_b.id = cte_table_a.id

I know its been a while since the original post but I like using CTE's and this worked for me:

WITH cte_table_a
AS
(
    SELECT [id] [id]
    , MAX([value]) [value]
    FROM table_a
    GROUP BY [id]
)
UPDATE table_b
SET table_b.code = CASE WHEN cte_table_a.[value] IS NOT NULL THEN cte_table_a.[value] ELSE 124 END
FROM table_b
LEFT OUTER JOIN  cte_table_a
ON table_b.id = cte_table_a.id

回复收藏 0 原文

~没有更多了~