Error in (very simple) delete query

Dissi

This is my (very simple) query :

DELETE FROM privatemessage
WHERE user_ID = '15'
ORDER BY date
LIMIT 1

And this is the result:

SQL-query:

DELETE FROM privatemessage WHERE user_ID = '15' ORDER BY date LIMIT 1
MySQL returned: You have an error in your SQL syntax near 'ORDER BY date LIMIT 1' at line 1

why ? 😕

gofrm

you don't need a 'order by' clause in a delete statement!

Dissi

But I do, I think

I want the last posted messages te be deleted.

That's why I sort all the messages bij date, The oldest posts wil be on top..

With LIMIT 1, only the first in row will be deleted. Thus, that would be de last post.

this is an example I found on www.mysql.com what does just the same I want.

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

gofrm

can you give me the exact link to this example, that is new for me, too.

i would add an autoincrement-field to the table 'privatemessage'
then you can delete the last record by executing this query:

DELETE FROM privatemessage
WHERE user_ID = '15'
AND ai_id = MAX(ai_id);

where ai_id is the auto-increment-field

Dissi

That is a posibility. But with my method I can delete more rows at a time, by giving an other value by LIMIT

(DELETE FROM privatemessage
WHERE user_ID = '15'
ORDER BY date
LIMIT 7

deleting the last 7 records )

This is the MYSQL manual:

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html

search for "DELETE FROM somelog"

If an ORDER BY clause is used, the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example:

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

This will delete the oldest entry (by timestamp) where the row matches the WHERE clause.

The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.

If I'm not mistaken, My code looks almost exactly the same as that example. But mine realy doesn't work..

gofrm

ORDER BY and using multiple tables in the DELETE statement is supported in MySQL 4.0.

do you have mysql 4.0 installed?

Dissi

Jes, Sorry, That's the problem.
Thanx.

So, now I'm workin on an alternative. Based on the query you posted earlier. But that doesn't seem to work either.

SQL-query:

DELETE FROM privatemessage WHERE user_ID = '15' AND ID = MAX(ID)

MySQL retourneerde: Invalid use of group function

ID is my autoincrement field.

My question is, wat will be the value of the MAX(ID). Will this be the MAX(ID) available in this table. Or the MAX(ID) from that table but te results are limited by user_ID=15 ?

What goes wrong here?

[deleted]

"My question is, wat will be the value of the MAX(ID). Will this be the MAX(ID) available in this table. Or the MAX(ID) from that table but te results are limited by user_ID=15 ? "

There won't be any value, because MAX() is not allowed in a DELETE query.

Not even MySQL let's you do that,

What you should do is find the highest id using a seperate query, and feed that to the delete query:

SELECT MAX(id) FROM table WHERE foo=bar;

// get $maxid

DELETE FROM table WHERE id=$maxid;