---

Hej

Hvad er fejlen i denne query:
DELETE FROM myEmails_POP3status
WHERE AccountID = 1 AND DownloadDate NOT IN
(SELECT DISTINCT DownloadDate
FROM myEmails_POP3status
WHERE AccountID = 1
ORDER BY DownloadDate DESC
LIMIT 0,2);

Det den gerne skulle gøre var at slette alle records der ikke har en
DownloadDate der er blandt de to nyeste.

Men når jeg kører den på en MySQL 3.23.54 server får jeg følgende fejl:
Invalid query: You have an error in your SQL syntax near 'SELECT DISTINCT
DownloadDate FROM myEmails_POP3status WHERE AccountID = 1 ORDER ' at line 1

Venlig Hilsen
Mads

---

On Sat, 6 Dec 2003 14:24:56 +0100, Mads wrote:

> Hvad er fejlen i denne query: DELETE FROM myEmails_POP3status
> WHERE AccountID = 1 AND DownloadDate NOT IN (SELECT DISTINCT
> DownloadDate FROM myEmails_POP3status WHERE AccountID = 1 ORDER BY
> DownloadDate DESC LIMIT 0,2);

Fejlen er ikke i query'en; det er nærmere en begrænsning i MySQL
- man kan ikke have sub-selects:

mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | C |
+----+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE id NOT IN (SELECT id FROM test WHERE name='A');
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM test WHERE name='A')' at line 1
mysql> SELECT * FROM test WHERE id NOT IN (1, 2, 3);
+----+------+
| id | name |
+----+------+
| 4 | C |
+----+------+
1 row in set (0.02 sec)

mysql>

(mysql Ver 12.22 Distrib 4.0.16)


(!),

--
"Do not feed the oysters under the clouds" Adam Sjøgren
asjo@koldfront.dk

---

"Mads" <mads@iname.com> wrote in news:bqsl9e$kcc$1@news.cybercity.dk:

> Men når jeg kører den på en MySQL 3.23.54 server får jeg følgende
> fejl: Invalid query: You have an error in your SQL syntax near 'SELECT
> DISTINCT DownloadDate FROM myEmails_POP3status WHERE AccountID = 1
> ORDER ' at line 1

http://www.mysql.com/doc/en/Rewriting_subqueries.html