Delete Single Record From Table

[jgd12345]

Hi, I know this sounds easy from the topic but what I'm wondering is how to delete just one record even if the criteria matches more than once.

For example say you have the table member_items:

uid | iid
1 | 2
1 | 2

Where uid is the members uid and iid is the items id. I want to be able to do "DELETE FROM member_items WHERE uid='1' and iid='2'" but I only want to delete one of them.

Please tell me if this is possible if not I'll have to add an id field which will add alot of extra work.

I'd be greatful for your help. Thanks

 

[rac2]

Is this a matter of deleting duplicate rows in order to clean up a table? Or is it an ongoing feature of your application?

For the cleanup one-time-only of duplicate rows you can define a new table and load it with DISTINCT rows from the old table.

INSERT myCleanTable (uid, iid)
SELECT DISTINCT uid, iid
FROM myMessyOldTable

If the rows are not duplicates then you should do the extra work and define a primary key for the table.

That might already exist. A primary key can consist of multiple columns. Is there a combination of columns that together uniquely identify a row? Simply use them all in the criteria for the DELETE query.

If your table does not have such a combination of columns then you should add a primary key column which could be an autonumber column. Use the new column as the criterion for deleting rows.

 

[jgd12345]

Ah k looks like the long way. I just got rid of the primary key field because I thought I didn't need it then I realized how would I deal with deleting them, if a user has more than one of the same item. Damn me.

Cheers for your help.

 

[cdlvj]

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

syntax will do it, if it does not matter which on, just eliminate the order by.