mysql的最大严格模式
问题描述:
我有以下查询:mysql的最大严格模式
DELETE FROM table1
WHERE node_id = ".$id."
AND date < (
SELECT
(MAX(date)- INTERVAL 1 MONTH)
from table1
WHERE node_id = ".$id."
)
然而,由于被启用的MySQL严格的模式我的错误:
ERROR 1093 (HY000): You can't specify target table 'table1' for update in FROM clause
调查后在线我试图重新作为查询如下:
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;
但是结果集返回空。
我已将max(tb2.date)
更改为手动输入的日期,这给了我期待的结果。
任何想法发生了什么?
答
你可以试试吗?
使用JOIN
数据
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)
检查
mysql> select * from test;
+---------+------------+
| node_id | dt |
+---------+------------+
| 1 | 2011-03-01 |
+---------+------------+
1 row in set (0.00 sec)
使用变量
在你的情况,你正在试图删除只有一个NODE_ID。这使得简单的查询。
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)
BTW
你问17个问题,但没有接受任何的答案。没有有用的答案吗?
尝试使用alice名称表 – bgs
谁!!“£%是爱丽丝? – Strawberry