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

发布于 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 (...);

分享到QQ

分享到微博

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

发布评论

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

爱的那么颓废 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!

回复收藏 0 原文

木緿 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
        )

回复收藏 0 原文

~没有更多了~