List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 16 1999 11:15am
Subject:Re: Delete first found record in SQL select
View as plain text  
Robert J Greene wrote:
> 
> This isn't a perfect solution, but you could alter the table to include a
> unique id column:
>         alter table aaa add column id int unsigned not null auto_increment, add key
> (id);
> (My test table was aaa -- real original, I know.) On my table, the new
> column had a unique id generated as part of the alter.
> 
> Then, you can use a two-step process to knock the row out:
>         select * from aaa where col1='abc' and col2='def' and col3='ghi' limit 1;
>         delete from aaa where id=1;
> 
> It would be pretty cool if the following would work:
>         delete from aaa where col1='abc' and col2='def' and col3='ghi' limit 1;
> However, the LIMIT phrase isn't valid in a delete.
> 
> Hope this helps a bit!
> -Rob
< cut > 

Hi Rob

You are wrong, LIMIT works with delete (at least with mysql 3.22.16).
I have made the following test to proove it:
-------------------------------------------------------------------
H:\Mack>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 3.22.16-gamma

Type 'help' for help.

mysql> create table del (id int(5), name char(40));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into del values(1,'first'),(2,'second'),(3,'third');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from del where name like '%d%';
+------+--------+
| id   | name   |
+------+--------+
|    2 | second |
|    3 | third  |
+------+--------+
2 rows in set (0.01 sec)

mysql> delete from del where name like '%d%' limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from del where name like '%d%';
+------+-------+
| id   | name  |
+------+-------+
|    3 | third |
+------+-------+
1 row in set (0.01 sec)

mysql>
--------------------------------------------------------------------------

Tschau
Christian

Thread
Delete first found record in SQL selectJann Linder12 Mar
  • Re: Delete first found record in SQL selectChristian Mack12 Mar
  • RE: Delete first found record in SQL selectRobert J Greene13 Mar
    • Re: Delete first found record in SQL selectChristian Mack16 Mar