A MySQL question

Ok, I want to delete only 1 row off of a database table...
Example:
I have a table with columns "user" and "item"...
Lets say the table contains the following rows (user | item):
582-668243 | Toothbrush
582-668243 | Toothbrush
582-668243 | Toothbrush
582-668243 | Trash can
582-668243 | Trash can
582-668243 | Something else
582-668243 | Something else
582-668243 | Something else
582-668243 | Something else
720-387690 | Dog treats
720-387690 | Car
720-387690 | Car
720-387690 | Toothbrush
720-387690 | Toothbrush

Ok, user 582-668243 is buying a lot, eh? LoL
Anyway, how can I remove only 1 Toothbrush from user 582-668243?
I just want the query... I've been trying to find it out for a few hours
now...

Thanks in advance!

--

- Zavaboy
zavaboy@hotmail.com
www.zavaboy.com

"Zavaboy" <zavaboy@hotmail.com> wrote in message
news:20030714211313.86984.qmail@pb1.pair.com...
> Ok, I want to delete only 1 row off of a database table...
> Example:
> I have a table with columns "user" and "item"...
> Lets say the table contains the following rows (user | item):
> 582-668243 | Toothbrush
> 582-668243 | Toothbrush
> 582-668243 | Toothbrush
> 582-668243 | Trash can
> 582-668243 | Trash can
> 582-668243 | Something else
> 582-668243 | Something else
> 582-668243 | Something else
> 582-668243 | Something else
> 720-387690 | Dog treats
> 720-387690 | Car
> 720-387690 | Car
> 720-387690 | Toothbrush
> 720-387690 | Toothbrush
>
> Ok, user 582-668243 is buying a lot, eh? LoL
> Anyway, how can I remove only 1 Toothbrush from user 582-668243?
> I just want the query... I've been trying to find it out for a few hours
> now...
>
> Thanks in advance!
>
> --
>
> - Zavaboy
> zavaboy@hotmail.com
> www.zavaboy.com


You can try putting "LIMIT 1" on the end of your query but to be honest I
don't know if LIMIT works with the DELETE command. Worth a try. Ideally
you should have an additional column with an autoincrementing value to act
as your primary key and you delete on that key.

- Kevin

--- zavaboy <zavaboy@hotmail.com> wrote:
> Lets say the table contains the following rows (user | item):
> 582-668243 | Toothbrush
> 582-668243 | Toothbrush
> 582-668243 | Toothbrush
....
> Anyway, how can I remove only 1 Toothbrush from user 582-668243?

With the information you provided, you can't. You should never have multiple
rows in the database that are non-unique, and this is one reason why. You need
a primary key.

> I just want the query

You should rather spend your time learning some database fundamentals rather
than asking this list (not a database list, in fact) for spoon-fed answers. You
will appreciate the knowledge more than the answer.

Chris

=====
Become a better Web developer with the HTTP Developer's Handbook
http://httphandbook.org/

zavaboy <zavaboy@hotmail.com> wrote:
> Ok, I want to delete only 1 row off of a database table...
> Example:
> I have a table with columns "user" and "item"...
> Lets say the table contains the following rows (user | item):
> 582-668243 | Toothbrush
> 582-668243 | Toothbrush

Note the 'LIMIT' part in the delete statment.
http://www.mysql.com/doc/en/DELETE.html

> [...]
>
> Thanks in advance!
>
> --
>
> - Zavaboy
> zavaboy@hotmail.com
> www.zavaboy.com
>

HTH
Curt
--

Ok, I added a primary key. I figured it out and I have it working... Thanks!

"Chris Shiflett" <shiflett@php.net> wrote in message
news:20030714212006.75107.qmail@web14308.mail.yaho o.com...
> --- zavaboy <zavaboy@hotmail.com> wrote:
> > Lets say the table contains the following rows (user | item):
> > 582-668243 | Toothbrush
> > 582-668243 | Toothbrush
> > 582-668243 | Toothbrush
> ...
> > Anyway, how can I remove only 1 Toothbrush from user 582-668243?
>
> With the information you provided, you can't. You should never have
multiple
> rows in the database that are non-unique, and this is one reason why. You
need
> a primary key.
>
> > I just want the query
>
> You should rather spend your time learning some database fundamentals
rather
> than asking this list (not a database list, in fact) for spoon-fed
answers. You
> will appreciate the knowledge more than the answer.
>
> Chris
>
> =====
> Become a better Web developer with the HTTP Developer's Handbook
> http://httphandbook.org/

You have made a mistake in your database design. Every table should have a
primary key that allows you to uniquely identify any single row in the
table. You do not have a primary key, hence when you want to delete a
single row from a series of duplicates, you can't do it. Unlike oracle,
mysql has no rowid.

-----Original Message-----
From: zavaboy [mailto:zavaboy@hotmail.com]
Sent: Monday, July 14, 2003 2:13 PM
To: php-general@lists.php.net
Subject: [php] A MySQL question

Ok, I want to delete only 1 row off of a database table...
Example:
I have a table with columns "user" and "item"...
Lets say the table contains the following rows (user | item):
582-668243 | Toothbrush
582-668243 | Toothbrush
582-668243 | Toothbrush
582-668243 | Trash can
582-668243 | Trash can
582-668243 | Something else
582-668243 | Something else
582-668243 | Something else
582-668243 | Something else
720-387690 | Dog treats
720-387690 | Car
720-387690 | Car
720-387690 | Toothbrush
720-387690 | Toothbrush

Ok, user 582-668243 is buying a lot, eh? LoL
Anyway, how can I remove only 1 Toothbrush from user 582-668243?
I just want the query... I've been trying to find it out for a few hours
now...

Thanks in advance!

--

- Zavaboy
zavaboy@hotmail.com
www.zavaboy.com



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php