更新满足与同一个表相关的条件的表记录

发布于 2024-11-18 00:00:31 字数 1233 浏览 0 评论 0原文

我正在尝试更新满足以下条件的记录的日期列:

  • 记录中的 STATION 相同
  • 列 STATION 必须与 FILTER_NR = x AND STATUS IN (11, 12, 13)
  • AND FILTER_NR != x

如果 x = 3我正在寻找的 UPDATE 语句应该将表过滤器从: 更改

+-----------+------------+---------+---------+
| FILTER_NR | PROBEDATE  | STATION |  STATUS |
+-----------+------------+---------+---------+
|         1 | 2011-06-01 |       1 |      10 |
|         2 | 2011-06-02 |       1 |      11 |
|         3 | 2011-06-03 |       1 |      12 |
|         4 | 2011-06-04 |       2 |      13 |
+-----------+------------+---------+---------+

为:

+-----------+------------+---------+----------+
| FILTER_NR | PROBEDATE  | STATION |  STATUS  |
+-----------+------------+---------+----------+
|         1 | 2011-06-01 |       1 |       10 | -> not changed
|         2 | 2011-06-01 |       1 |       11 | -> changed
|         3 | 2011-06-03 |       1 |       12 | -> not changed
|         4 | 2011-06-04 |       2 |       13 | -> not changed
+-----------+------------+---------+----------+

我从以下 SQL 语句开始,你知道我如何完成它吗?

UPDATE FILTER SET PROBEDATE = ADDDATE(PROBEDATE, -1)
WHERE FILTER_NR IN (...);

I'm trying to update a date column of records satisfying following condition:

  • Column STATION has to be the same as STATION from record with FILTER_NR = x
  • AND STATUS IN (11, 12, 13)
  • AND FILTER_NR != x

If x = 3 the UPDATE Statement, I'm looking for, should change the Table FILTER from:

+-----------+------------+---------+---------+
| FILTER_NR | PROBEDATE  | STATION |  STATUS |
+-----------+------------+---------+---------+
|         1 | 2011-06-01 |       1 |      10 |
|         2 | 2011-06-02 |       1 |      11 |
|         3 | 2011-06-03 |       1 |      12 |
|         4 | 2011-06-04 |       2 |      13 |
+-----------+------------+---------+---------+

to:

+-----------+------------+---------+----------+
| FILTER_NR | PROBEDATE  | STATION |  STATUS  |
+-----------+------------+---------+----------+
|         1 | 2011-06-01 |       1 |       10 | -> not changed
|         2 | 2011-06-01 |       1 |       11 | -> changed
|         3 | 2011-06-03 |       1 |       12 | -> not changed
|         4 | 2011-06-04 |       2 |       13 | -> not changed
+-----------+------------+---------+----------+

I began with following SQL Statement, do you know how I can complete it?

UPDATE FILTER SET PROBEDATE = ADDDATE(PROBEDATE, -1)
WHERE FILTER_NR IN (...);

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

扫码二维码加入Web技术交流群

发布评论

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

评论(2

爱的那么颓废 2024-11-25 00:00:31

这应该适合您:

UPDATE `FILTER` `F`
INNER JOIN `FILTER` `F1` ON `F1`.`FILTER_NR` = 3 AND `F1`.`STATION` = `F`.`STATION`
SET `F`.`PROBEDATE` = CURDATE()
WHERE `F`.`FILTER_NR` != 3
AND `F`.`STATUS` IN (11, 12, 13);

在我的示例中,我已将 PROBEDATE 设置为当前日期,但请随意将其设置为您可能喜欢的值。

希望这有帮助!

This should work for you:

UPDATE `FILTER` `F`
INNER JOIN `FILTER` `F1` ON `F1`.`FILTER_NR` = 3 AND `F1`.`STATION` = `F`.`STATION`
SET `F`.`PROBEDATE` = CURDATE()
WHERE `F`.`FILTER_NR` != 3
AND `F`.`STATUS` IN (11, 12, 13);

In my example, I've set PROBEDATE to the current date but please feel free to set it to what you might like.

Hope this helps!

木緿 2024-11-25 00:00:31

你可以这样输入:

UPDATE  FILTER 
SET     PROBEDATE = PROBEDATE - inteval 1 day
WHERE   STATUS IN (11,12,13)
        AND FILTER_NR != 3
        AND STATION IN
        (
        SELECT  STATION
        FROM    (
                SELECT  *
                FROM    FILTER
                ) as SubQueryAlias
        WHERE   FILTER_NR = 3
        )

You could ty something like this:

UPDATE  FILTER 
SET     PROBEDATE = PROBEDATE - inteval 1 day
WHERE   STATUS IN (11,12,13)
        AND FILTER_NR != 3
        AND STATION IN
        (
        SELECT  STATION
        FROM    (
                SELECT  *
                FROM    FILTER
                ) as SubQueryAlias
        WHERE   FILTER_NR = 3
        )
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文