Sign in to follow this  
benryves

[web] What's up with this query? (MySQL)

Recommended Posts

DELETE FROM items_temporary WHERE id=65 ORDER BY issue_date LIMIT 2 As a solution to an earlier problem I posted on here, I dump all records into a temporary table then remove all of the old ones. It's inelegant, but more than fast enough for the amount of data I'm dealing with. items_temporary is a temporary table. id and issue_date are on it. This query executes perfectly under MySQL 4.1, fails under 3.23: 1064: You have an error in your SQL syntax near 'ORDER BY issue_date LIMIT 2' at line 1 If I remove ORDER BY issue_date (and leave LIMIT 2) in, the query executes, but deletes the wrong rows - I need to order them.

Share this post


Link to post
Share on other sites
Why do you use "order by" in a delete query?
Ther is no reason for it. It shoudn't work iven in later versions.

put just DELETE FROM items_temporary WHERE id=65
if it fails try to qualify the table and the data bank

for example
DELETE FROM yourDB.items_temporary WHERE id=65

Share this post


Link to post
Share on other sites
Quote:
Original post by manuelb
Why do you use "order by" in a delete query?
Ther is no reason for it. It shoudn't work iven in later versions.

There is every reason for it. My method is a bit ugly, but it's the best I could come up with under the circumstances.

id is not unique. It's an item id (cross reference).

id | date | owner
---+------+----------
1 | 2005 | abc
2 | 2005 | def
2 | 2006 | ghi
2 | 2007 | jkl

...where id is the item id, date is the date it was issued. I only want to display the "current" owner, so I can do this by (in this case):

DELETE FROM table WHERE id = 2 ORDER BY date LIMIT 2

This would leave me with:

id | date | owner
---+------+----------
1 | 2005 | abc
2 | 2007 | jkl


DELETE FROM yourDB.items_temporary WHERE id=65 would delete all of the matching records for id=65. I need to leave one behind, hence the ORDER BY and LIMIT.


Edit: Bah, in the tiny print: "ORDER BY can be used with DELETE beginning with MySQL 4.0.0." I guess I need to find another solution. (Delete row-by-row using PHP, hooray).

Share this post


Link to post
Share on other sites
Ok, o got it. You'll have to use nasted querys.
But it will no be good for performance when you'll have a lot of ownerships.
My suggestion is to change your database design:

Table ownership:
ownership_id(pk) | item_id | user_id | date
-----------------+---------+---------+---------------------
0000000000000001 | 0000001 | 0000001 | 17/11/2006 14:30:00
0000000000000002 | 0000002 | 0000003 | 12/11/2006 17:30:00
0000000000000003 | 0000002 | 0000007 | 20/11/2006 25:32:00

table Item
item_id(pk) | etc... | ownership_id(fk)
------------+--------+------------------
00000000001 | etc... + 0000000000000001
00000000002 | etc... + 0000000000000003

it will be fast to kow who is the last owner of an item. Ownership is no only the relationship of 2 tables. Its a comcept by itself and shoud have a especific id for it.

Hope that helps

Manuel.

Share this post


Link to post
Share on other sites
A lot of the messiness is due to the ancient version of the database server software. This thread discusses it - the easiest way I found was to dump all records into a temporary table, duplicates and all, then delete the oldest duplicates. The table in my example, with duplicate id numbers, is this temporary intermediate table.

Share this post


Link to post
Share on other sites
The order-by clause doesn't work with delete. I guess you know that by now.

But, Couldn't you just do a sub-query? I know its performance isn't the greatest, but maybe something like this:

Delete from Table1 where ID = 65 and Date not in (Select * from Table1 where ID = 65 order by Date desc limit 0,1)

Conversly, why are you storing the old owners in the same table? Why not have a new table that stores all the old owner data, and another table that only stores the most current owner? That way, you can do:

Delete from OwnerHistory where to_days(Now()) - to_days(Date) > 365 * 4 (ie, greater than 4 years)

The above will let you trim out a lot nicer in the future... not that you should really need to delete owner history anyways.

Share this post


Link to post
Share on other sites
Well, I think the only way is to use nasted queryes. for each item, select the item last ownership, delete all the onerships of this item except that one you've selected before

select item id from itmes;
for item 1 to n
{
vdata:= select date from temptable where item_id = id order by date desc limit 1;

delete from temptable where item_id = id and data <> vdata;

}

Well, maybe find a faster way, but you got the ideia.

Share this post


Link to post
Share on other sites
Quote:
Original post by Cygnus_X
The order-by clause doesn't work with delete. I guess you know that by now.

But, Couldn't you just do a sub-query? I know its performance isn't the greatest, but maybe something like this:

Delete from Table1 where ID = 65 and Date not in (Select * from Table1 where ID = 65 order by Date desc limit 0,1)

Conversly, why are you storing the old owners in the same table? Why not have a new table that stores all the old owner data, and another table that only stores the most current owner? That way, you can do:

Delete from OwnerHistory where to_days(Now()) - to_days(Date) > 365 * 4 (ie, greater than 4 years)

The above will let you trim out a lot nicer in the future... not that you should really need to delete owner history anyways.



I didn't know MySQL suports subquerys, thats realy good! I used to use it in Oracle. Since MYSql suports it, your solution is the best one.

Share this post


Link to post
Share on other sites
Quote:
It only works as of MySql 5.0... so check your version before putting too much effort into it.


Actually subqueries have been supported since version 4.1

Share this post


Link to post
Share on other sites
a) Create temp table
b) Insert top 2 records (select * from items_temporary WHERE id=65 ORDER BY issue_date LIMIT 2)
c) Delete from your source table joining to the second table
d) DELETE x from items_temporary x join items_temporary_2 y on x.Key = y.Key

(Obviously Key is whatever your PK colums are...)

Dunno if this would work for mysql, i only use T-SQL these days

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this