[web] What's up with this query? (MySQL)
Author
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.
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
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
Author
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 | jklDELETE 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).
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.
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.
Author
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.
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.
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.
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.
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.
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.
It only works as of MySql 5.0... so check your version before putting too much effort into it.
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
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
