SQL修改问题

发布于 2024-10-17 23:04:31 字数 418 浏览 4 评论 0原文

如果不活动时间超过 30 天，我需要替换 SQL 中表的大量值。

我有

UPDATE VERSION 
   SET isActive = 0
 WHERE customerNo = (SELECT c.VersionNo
                       FROM Activity b 
                      INNER JOIN VERSION c ON b.VersionNo = c.VersionNo
                      WHERE (Months_between(sysdate, b.Activitye) > 30));

它只适用于一个值，但如果返回的值超过一个，则会失败。我在这里缺少什么？

如果有人可以告诉我正在发生的事情，我也会很感激。

原文

I need to replace a lot of values for a Table in SQL if the inactivity is greater then 30 days.

I have

UPDATE VERSION 
   SET isActive = 0
 WHERE customerNo = (SELECT c.VersionNo
                       FROM Activity b 
                      INNER JOIN VERSION c ON b.VersionNo = c.VersionNo
                      WHERE (Months_between(sysdate, b.Activitye) > 30));

It only works for one value though, if there is more then one returned it fails. What am I missing here?

If someone could educate me on what is going on, I'd also appreciate it.

分享到QQ

分享到微博

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

发布评论

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

吝吻 2024-10-24 23:04:31

您使用 WHERE customerNo = (SELECT ...); 这仅适用于 1 个值。尝试使用 WHERE customerNo IN (SELECT ...);

回复收藏 0 原文

我的鱼塘能养鲲 2024-10-24 23:04:31

有多种方法可以处理子查询返回多行的情况。要解决的问题是子查询返回多个值是否正确，或者它是否应该只返回一行。针对子查询的等于运算符期望仅返回一行/记录。

此外，MONTHS_BETWEEN 返回两个日期之间的月份。如果您想查看天数，请使用b.activity - SYSDATE > 30，或者反之亦然（如果适用）。

假设多行有效 -

IN 子句子

查询中无需对 VERSION 表进行第二次联接：

UPDATE VERSION 
   SET isActive = 0
 WHERE customerNo IN (SELECT c.customerno
                        FROM Activity b 
                       WHERE b.VersionNo = VERSION.VersionNo
                         AND MONTHS_BETWEEN(SYSDATE, b.activity) > 30);

VERSION 表别名指的是正在更新的表。

EXISTS 子句

UPDATE VERSION 
   SET isActive = 0
 WHERE EXISTS(SELECT NULL
                FROM ACTIVITY a
               WHERE VERSION.customerno = a.customerno
                 AND VERSION.versionno = a.versionno
                 AND MONTHS_BETWEEN(SYSDATE, b.activity) > 30);

只能有一个吗？

如果只应返回一行，则可以考虑适当的聚合函数（MIN、MAX）。

There are numerous ways of handling that a subquery returns more than one row. The issue to address is if the subquery returning multiple values is correct, or if it should only ever return one row. The equals operator against a subquery expects that only one row/record will be returned.

Also, MONTHS_BETWEEN returns the months between the two dates. If you want to see the number of days, use b.activity - SYSDATE > 30, or vice versa if appropriate.

Assuming more than one row is valid -

IN clause

There's no need for the 2nd join in the subquery to the VERSION table:

UPDATE VERSION 
   SET isActive = 0
 WHERE customerNo IN (SELECT c.customerno
                        FROM Activity b 
                       WHERE b.VersionNo = VERSION.VersionNo
                         AND MONTHS_BETWEEN(SYSDATE, b.activity) > 30);

The VERSION table alias refers to the table being updated.

EXISTS clause

UPDATE VERSION 
   SET isActive = 0
 WHERE EXISTS(SELECT NULL
                FROM ACTIVITY a
               WHERE VERSION.customerno = a.customerno
                 AND VERSION.versionno = a.versionno
                 AND MONTHS_BETWEEN(SYSDATE, b.activity) > 30);

There can be only one?

If only one row should be returned, the appropriate aggregate function (MIN, MAX) might be considered.

回复收藏 0 原文

梦行七里 2024-10-24 23:04:31

替换

WHERE customerNo =

为

WHERE customerNo IN

Replace

WHERE customerNo =

with

WHERE customerNo IN

回复收藏 0 原文

深府石板幽径 2024-10-24 23:04:31

MERGE INTO VERSION dest
USING (SELECT ver.VersionNo
FROM Activity act 
INNER JOIN VERSION ver ON act.VersionNo = var.VersionNo
WHERE (Months_between(sysdate, act.Activitye) > 30)) src
ON (scr.customerNo = dest.customerNo)
WHEN MATCHED THEN
UPDATE SET isActive = 0

MERGE INTO VERSION dest
USING (SELECT ver.VersionNo
FROM Activity act 
INNER JOIN VERSION ver ON act.VersionNo = var.VersionNo
WHERE (Months_between(sysdate, act.Activitye) > 30)) src
ON (scr.customerNo = dest.customerNo)
WHEN MATCHED THEN
UPDATE SET isActive = 0

回复收藏 0 原文

~没有更多了~