Update table records satisfying a condition related to the same table

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 |   开发者_如何学C    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 (...);

---

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!

---

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
        )