更新满足与同一个表相关的条件的表记录
我正在尝试更新满足以下条件的记录的日期列:
- 记录中的 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这应该适合您:
在我的示例中,我已将 PROBEDATE 设置为当前日期,但请随意将其设置为您可能喜欢的值。
希望这有帮助!
This should work for you:
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!
你可以这样输入:
You could ty something like this: