Professional Web Applications Themes

How to delete the first entry of a table - MySQL

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...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: How to delete the first entry of a table

    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.
    Bill Karwin Guest

  3. #3

    Default Re: How to delete the first entry of a table

    You could simply replace the keyword SELECT with DELETE:

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

    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.
    Dikkie Dik Guest

  4. #4

    Default Re: How to delete the first entry of a table

    > DELETE FROM mytable WHERE key_mycode = ?

    Works perfect, many thanks
    Stefan


    Stefan Mueller Guest

  5. #5

    Default Re: How to delete the first entry of a table

    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


    Markus Popp Guest

Similar Threads

  1. Lookup for table entry
    By Paul Lautman in forum MySQL
    Replies: 3
    Last Post: August 15th, 03:28 PM
  2. Delete form - Post data to a table and delete uponsubmit.
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 28th, 01:27 PM
  3. [net::ldap] how to delete entry like this
    By news.hinet.net in forum PERL Modules
    Replies: 0
    Last Post: June 21st, 01:30 AM
  4. Delete key doesn't delete when datagrid is bound to a disconnected table
    By Fred Zolar in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: April 1st, 07:47 AM
  5. Delete from one table with matching records in another table
    By Dale Fye in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 1st, 01:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139