How to delete the first entry of a table

I know that I can read the first row of a table with
$db_query = "SELECT * FROM mytable ORDER BY key_mycode ASC LIMIT 0, 1";

But how can I delete this entry (the whole row)?
Stefan

Stefan Mueller wrote:
> I know that I can read the first row of a table with
> $db_query = "SELECT * FROM mytable ORDER BY key_mycode ASC LIMIT 0, 1";
>
> But how can I delete this entry (the whole row)?

Use the DELETE statement. ;-)

You can use your SELECT query to get the row with the lowest value in
key_mycode (I phrase it that way because database purists get twitchy
when you talk about the "first" record in a table). Then you can use
the value from that query to form the next DELETE statement:

DELETE FROM mytable WHERE key_mycode = ?

Where I have "?" above, put in the value from the key_mycode field
returned by your SELECT query.

In some RDBMS implementations, you could use subqueries like this:

DELETE FROM mytable
WHERE key_mycode = (SELECT MIN(key_mycode) FROM mytable);

But alas, MySQL cannot do that; you can't select from a table in the
same statement that you delete from that table.

Here's a really fancy method, using an outer join in MySQL's extended
syntax for multi-table deletes:

DELETE FROM m1
USING mytable AS m1 LEFT OUTER JOIN mytable AS m2
ON m1.key_mycode > m2.key_mycode
WHERE m2.key_mycode IS NULL;

In other words, "delete from mytable all rows for which there is no row
with a lower value for key_mycode."

Regards,
Bill K.

You could simply replace the keyword SELECT with DELETE:

http://dev.mysql.com/doc/refman/4.1/en/delete.html

Bill Karwin wrote:
> Stefan Mueller wrote:
>
>> I know that I can read the first row of a table with
>> $db_query = "SELECT * FROM mytable ORDER BY key_mycode ASC LIMIT 0, 1";
>>
>> But how can I delete this entry (the whole row)?
>
>
> Use the DELETE statement. ;-)
>
> You can use your SELECT query to get the row with the lowest value in
> key_mycode (I phrase it that way because database purists get twitchy
> when you talk about the "first" record in a table). Then you can use
> the value from that query to form the next DELETE statement:
>
> DELETE FROM mytable WHERE key_mycode = ?
>
> Where I have "?" above, put in the value from the key_mycode field
> returned by your SELECT query.
>
> In some RDBMS implementations, you could use subqueries like this:
>
> DELETE FROM mytable
> WHERE key_mycode = (SELECT MIN(key_mycode) FROM mytable);
>
> But alas, MySQL cannot do that; you can't select from a table in the
> same statement that you delete from that table.
>
> Here's a really fancy method, using an outer join in MySQL's extended
> syntax for multi-table deletes:
>
> DELETE FROM m1
> USING mytable AS m1 LEFT OUTER JOIN mytable AS m2
> ON m1.key_mycode > m2.key_mycode
> WHERE m2.key_mycode IS NULL;
>
> In other words, "delete from mytable all rows for which there is no row
> with a lower value for key_mycode."
>
> Regards,
> Bill K.

> DELETE FROM mytable WHERE key_mycode = ?

Works perfect, many thanks
Stefan

You can also use LIMIT in a DELETE statement - although deletion usually
works faster if you can specify the ID which uses a primary key.

Markus