Re: Record IDs
2009/2/23 Hagen Finley finha...@comcast.net: I have a number of duplicate records in my table which are identical hence, I can't do a delete on the columns without deleting both records. One deletion strategy I have considered is identifying the records by their table record id - but I don't know for certain that such an identifier exists or how I would show it via a select statement. Any suggestions? DELETE FROM yourtable WHERE foo=bar LIMIT 1 should do the job. Greetings, Mattia Merzi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record IDs
On Mon, Feb 23, 2009 at 11:40 AM, Hagen Finley finha...@comcast.net wrote: I have a number of duplicate records in my table which are identical hence, I can't do a delete on the columns without deleting both records. One deletion strategy I have considered is identifying the records by their table record id - but I don't know for certain that such an identifier exists or how I would show it via a select statement. Any suggestions? Hagen Finley Boulder, CO If you do not want to delete duplicates one at a time (i.e. LIMIT 1), then you need a unique record identifier. MySQL does not have a default unique record id, you need to create that field yourself. Just specify it as an auto-increment and you with have a unique ID for each record. Check if you already have an auto-increment field, otherwise add one. To delete your duplicate records, use something similar to the following query. DELETE tableName FROM tableName JOIN (SELECT max(recordID) dupID, count(*) c FROM tableName GROUP BY recordID HAVING c1) AS dupTable ON tableName.recordID=dupTable.dupID That will delete the latest duplicate record. If you have more than 2 copies of a record (i.e. triplicates), this will not clean the all out. You can keep running the query to delete multiple duplicates of records. Test the query first to make sure it's working properly. Just switch DELETE tableNAME with SELECT fieldName(s). Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Record IDs
-Original Message- From: Hagen Finley [mailto:finha...@comcast.net] Sent: Monday, February 23, 2009 11:41 AM To: 'MySql' Subject: Record IDs I have a number of duplicate records in my table which are identical hence, I can't do a delete on the columns without deleting both records. One deletion strategy I have considered is identifying the records by their table record id - but I don't know for certain that such an identifier exists or how I would show it via a select statement. Any suggestions? [JS] If the table isn't too large, you might be able to CREATE TABLE holder SELECT DISTINCT * FROM doubles [WHERE ...]; DELETE FROM doubles [WHERE ...] INSERT INTO doubles SELECT * FROM holder; Hagen Finley Boulder, CO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
