visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: PHP :: How to use DELETE with LIMIT
By: DImirC U.S.A.  Date: 23/07/2003 00:00:00  English  Points: 125 Status: Answered
Quality : Excellent
I want to delete the records that are after the No. 10 what I tried was this:

delete from table1 order by field1 limit 10

But it deletes the first 10...
By: Gumbo Date: 23/07/2003 21:52:00 English  Type : Comment
DELETE FROM table1 WHERE field1>10
By: Andy_Neodynium Date: 23/07/2003 22:05:00 English  Type : Comment
No, Field1 might only have values greater than 10, or that aren't numeric.

Try:

DELETE FROM table1 ORDER BY field1 LIMIT 10, -1

This should have an offset of 10 (missing the values you want) and the -1 tells it to continue to the end of the set...
By: Andy_Neodynium Date: 23/07/2003 22:05:00 English  Type : Comment
Oh, I should have said, that's in MySQL at least
By: waygood Date: 23/07/2003 22:30:00 English  Type : Comment
try this
delete from table1 order by field1 limit 10,0
By: waygood Date: 23/07/2003 22:32:00 English  Type : Comment
sorry for almost duplicating I misread Andys comments
By: VGR Date: 23/07/2003 23:28:00 English  Type : Answer
"delete the records that are after the No. 10 " : this is senseless in SQL

rows don't have a "number" (don't tell me about row_id, it's not standard SQL)

databases are relational, not an ordered set of records like in an old file-based system

the answer of waygood is the correct one : first order your records as you wish with a condition, then apply the DELETE FROM... LIMIT 10;

if you had introduced (good practice :D ) a (usually first) field/column named "id" and of type "integer unique auto_increment", you would indeed been able to "delete past the tenth element" by doing :

CREATE TABLE tempo AS SELECT * FROM table1 ORDER BY id ASC LIMIT 10;
TRUNCATE table1; # or : DELETE FROM table1;
INSERT INTO table1 SELECT * FROM tempo;
DROP TABLE tempo;
By: DImirC Date: 23/07/2003 23:54:00 English  Type : Comment
Can I use Order in and Delete together?

I'm trying this:
DELETE FROM table1 WHERE type= e ORDER BY stamp ASC LIMIT 2
By: carchitect Date: 24/07/2003 05:09:00 English  Type : Comment
sorry your question is wrong
you can not apply limit clause with delete query
By: waygood Date: 24/07/2003 18:58:00 English  Type : Comment
Check your facts first carchitect.

----QUOTE FROM <A HREF="http://WWW.MYSQL.COM----">WWW.MYSQL.COM----</a>

6.4.6 DELETE Syntax

DELETE [LOW_PRIORITY] [QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]

or

DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
FROM table-references
[WHERE where_definition]

or

DELETE [LOW_PRIORITY] [QUICK]
FROM table_name[.*] [, table_name[.*] ...]
USING table-references
[WHERE where_definition]
.
.
.
.
If an ORDER BY clause is used (available from MySQL 4.0.0), 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 row_count 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.

From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. However, you can not use ORDER BY or LIMIT in a multi-table DELETE.


By: VGR Date: 24/07/2003 19:50:00 English  Type : Comment
of course you can, carchitetc :D

It's even the most handy feature of MySql's dialect :D

very handy to delete one of the duplicates only...
By: DImirC Date: 24/07/2003 20:22:00 English  Type : Comment
I'm trying this:

DELETE FROM table1 WHERE t = 'e' ORDER BY stamp LIMIT 1

But I get an error...
By: waygood Date: 24/07/2003 20:49:00 English  Type : Comment
what version of mysql are you using, as you must have 4+ to use it.

If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be deleted in that order. This is really only useful in conjunction with LIMIT.

By: VGR Date: 24/07/2003 21:44:00 English  Type : Comment
true ; 3.23.* uses only DELETE FROM ... WHERE ... LIMIT 1;
By: jamesm87 Date: 26/07/2003 03:59:00 English  Type : Comment
Maybe give us the error you get.

You might want to check that any fields are t = 'e'.
By: VGR Date: 29/07/2003 04:24:00 English  Type : Comment
give the answer to waygood please

I don't want to steal anyone

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 368.893860 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page