I have the following query:
DELETE FROM table1
WHERE node_id = ".$id."
AND date < (
SELECT
(MAX(date)- INTERVAL 1 MONTH)
from table1
WHERE node_id = ".$id."
)
However due to mysql strict mode being enabled i get the error:
ERROR 1093 (HY000): You can't specify target table 'table1' for update in FROM clause
After investigation online i attempted to recreate the query as below:
SELECT
*
FROM table1 as tb1
INNER JOIN table1 as tb2
on tb1.id = tb2.id
HAVING tb1.date < MAX(tb2.date)-INTERVAL 1 MONTH AND tb1.node_id = 1;
However the result set is returning empty.
I have changed max(tb2.date)
to a manually entered date which gave me the results I was expecting.
Any ideas what is going on here?
could you try this?
Data
mysql> select * from test;
+---------+------------+
| node_id | dt |
+---------+------------+
| 1 | 2011-03-01 |
| 1 | 2011-02-01 |
| 1 | 2011-01-01 |
+---------+------------+
3 rows in set (0.00 sec)
SELECT
SELECT *
FROM (
SELECT MAX(dt) AS max_dt
FROM test
WHERE node_id = 1
) x INNER JOIN test ON test.node_id = 1 AND test.dt < x.max_dt;
+------------+---------+------------+
| max_dt | node_id | dt |
+------------+---------+------------+
| 2011-03-01 | 1 | 2011-01-01 |
| 2011-03-01 | 1 | 2011-02-01 |
+------------+---------+------------+
DELETE
DELETE test
FROM (
SELECT MAX(dt) AS max_dt
FROM test
WHERE node_id = 1
) x INNER JOIN test ON test.node_id = 1 AND test.dt < x.max_dt;
Query OK, 2 rows affected (0.02 sec)
Check
mysql> select * from test;
+---------+------------+
| node_id | dt |
+---------+------------+
| 1 | 2011-03-01 |
+---------+------------+
1 row in set (0.00 sec)
In your case, you are trying to delete just one node_id. That makes simple query.
SELECT @max_dt := MAX(dt)
FROM test
WHERE node_id = 1;
+--------------------+
| @max_dt := MAX(dt) |
+--------------------+
| 2011-03-01 |
+--------------------+
DELETE FROM test
WHERE node_id = 1
AND dt < @max_dt;
Query OK, 2 rows affected (0.00 sec)
You asked 17 questions but hadn't accepted any answer. Are there no useful answers?