Re: JOIN giving duplicate records
; 2012/04/03 18:18 +0100, Tompkins Neil Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7) and if I add additional IDs like AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7 OR hotel_facilities.hotelfacilitytype_id = 8), it would return three records like below when I'm expecting one record. 100,UK,12121 100,UK,12121 100,UK,12121 However in my SELECT statement if I use SELECT DISTINCT (field_name), I only get back one record. It is in JOIN s nature to multiply output, but unless you show your whole query, noöne can comment on it. As for DISTINCT, its purpose is as you saw, to eliminate duplicates. Sometimes it is the best means for it--but you show too little for comment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
JOIN giving duplicate records
Hi Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7) and if I add additional IDs like AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7 OR hotel_facilities.hotelfacilitytype_id = 8), it would return three records like below when I'm expecting one record. 100,UK,12121 100,UK,12121 100,UK,12121 However in my SELECT statement if I use SELECT DISTINCT (field_name), I only get back one record. Thanks, Neil
How to get the first data from a multiple or duplicate records
Hello everyone, I would like to ask for idea and help on how to achieve my concern. Below is my SQL statement. Im joining 2 tables to get my results. Here's the sample results of what im getting. Name | Desc | Issue | ATime | Back | TotalTime | Ack | Res 123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:21:33 | 00:02:07 | u...@home.net | fixed 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:25:33 | 00:02:07 | u...@home.net | fixed 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 18:20:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 19:29:33 | 00:02:07 | u...@home.net | fixed What I want to achieve is to get only the first entry of data based on Back and do my calculation. how can I do that? 123 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 234 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed 223 | test | error | 2011-10-18 17:09:26 | 2011-10-18 17:11:33 | 00:02:07 | u...@home.net | fixed Here's my statement. SELECT t1.name, t1.Description, t1.Issue, t1.Dateres AS ATime, t2.Dateres AS BAck, TIMEDIFF(t2.Dateres,t1.Dateres) AS TotalTime, t2.Acknowledge, t2.Resolution FROM t1 LEFT JOIN t2 ON t1.name = t2.name AND t1.IPAddress = t2.IPAddress AND t1.Description = t2.Description AND t1.Issue = t2.Issue AND t1.Severity = t2.Severity AND t1.Timestamp = t2.Timestamp WHERE t1.Dateres is NOT NULL AND t2.Dateres is NOT NULL AND t2.Acknowledge = 'u...@home.net' AND t2.Dateres = '2011-10-18 00:00:00' AND t2.Dateres = '2011-10-23 23:59:59' GROUP BY ATime ORDER by BAck ASC; Thanks.
Re: sql to duplicate records with modified value
thanks, Ray, that worked well (btw, you have a typo, 'Independant' instead of 'Independent') btw2, I have a pdf with some 15,000 names that I would like to display with a search function, I email you later, maybe you can help me with that -- Voytek quote who=Ray Cauchi Hi Voytek You could try some variation of: INSERT INTO inserttable (user, maildir) SELECT REPLACE(user, '@', 'spam@') as user, CONCAT(maildir,'.spam/') as maildir FROM selecttable [WHERE ..] the where bit is optional of course! let me know how you go - hope you are keeping well! ray At 03:17 PM 27/03/2010, Voytek Eymont wrote: I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@tweek.com.au ( T W E E K ! ) PO Box 15 Wentworth Falls NSW Australia 2782 | p:+61 2 4702 6377 (Sydney/Penrith/Blue Mountains) | p:+61 2 4915 8532 (Newcastle/Hunter) | f:+61 2 8456 5743 | m:0414 270 400 | e:ray at tweek dot com dot au | w:www dot tweek dot com dot au 'What is more immoral than war?' - Marquis de Sade -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sql to duplicate records with modified value
Voytek Eymont wrote: I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) Are you hoping to do all that you want - copy rows, update rows and create new rows - in a single SQL statement? Because if that's what you want, I don't think it's possible. Unless someone has come up with some new tricks, you can't insert a new record and update an existing one within the same SQL statement. If you want to do it via several SQL statements, each part of what you want should be possible via different statements in a script where you can use the script itself to help with the update logic. If you want to do the updates from the command line only and won't consider a program, I don't know how to do it. I should point out that I'm quite fluent in SQL and have been writing it for a long time. I haven't used MySQL in a few years now but I'm still on the mailing list and your question caught my eye. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sql to duplicate records with modified value
quote who=Rhino Voytek Eymont wrote: Are you hoping to do all that you want - copy rows, update rows and create new rows - in a single SQL statement? Because if that's what you want, I don't think it's possible. Unless someone has come up with some new tricks, you can't insert a new record and update an existing one within the same SQL statement. If you want to do it via several SQL statements, each part of what you want should be possible via different statements in a script where you can use the script itself to help with the update logic. If you want to do the updates from the command line only and won't consider a program, I don't know how to do it. I should point out that I'm quite fluent in SQL and have been writing it for a long time. I haven't used MySQL in a few years now but I'm still on the mailing list and your question caught my eye. Rhino, thanks a script would be great, if you can help with it, I'd be grateful. I'm not sure where/how to start, the username is a primary index. or do I run a dump, then, work some script over dump, and, re-import ? -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sql to duplicate records with modified value
Hi Voytek You could try some variation of: INSERT INTO inserttable (user, maildir) SELECT REPLACE(user, '@', 'spam@') as user, CONCAT(maildir,'.spam/') as maildir FROM selecttable [WHERE ..] the where bit is optional of course! let me know how you go - hope you are keeping well! ray At 03:17 PM 27/03/2010, Voytek Eymont wrote: I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@tweek.com.au ( T W E E K ! ) PO Box 15 Wentworth Falls NSW Australia 2782 | p:+61 2 4702 6377 (Sydney/Penrith/Blue Mountains) | p:+61 2 4915 8532 (Newcastle/Hunter) | f:+61 2 8456 5743 | m:0414 270 400 | e:ray at tweek dot com dot au | w:www dot tweek dot com dot au 'What is more immoral than war?' - Marquis de Sade
sql to duplicate records with modified value
I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Removing Duplicate Records
In our database we have an Organizations table and a Contacts table, and a linking table that associates Contacts with Organizations. Occassionally we manually add to the linking table with information gleaned from outside data sources. This is common enough to be annoying, since it ends up with duplicate linkages, but it's FAR from an everyday occurance. I have three options for dealing with the resulting duplicates and I would appreciate some advice on which option might be best. 1. Attack the horrific spaghetti code that determines the Org and Contact ids and then does the manual add. Creating Orgs and Contacts as needed. Calling this code horrific is a kindness... we're talking evil... We've pretty much ruled this out due to the horror... but I mention that I considered it. 2. Do a create table and populate that new table with the results of a select distinct from the old table then swap the tables. 3. Do... SELECT count(*) AS tCount,OrgID,ContID FROM OrgContLink GROUP BY OrgID,ContID HAVING tCount 1. Then for every record in the result DELETE FROM OrgContLink WHERE OrgID=X AND ContID=Y LIMIT 1. Then repeat until no results are found. I like option 2 in so far as it's more... Atomic? One create...select, one swap and its done. But even though it feels more pure I worry that the overhead of completely creating and destroying entire tables seems like throwing the baby out with the bathwater. IOW: Is rebuilding a whole table for a few (hundred at most) offending duplicate records overkill. I like option 3 in that it leaves everything as is but does require a lot of looping and feels inefficient. However, since we'd be running this only after we do our imports it's not like this looping inefficient code would be running all the time. I know I could probably also put a unique key on both orgid and contid but unless I'm reading the docs wrong I can't add a key in such a way that the duplicate key insertion would silently fail without requiring the offending application to do INSERT ... ON DUPLICATE KEY... which gets back to modifying the spaghetti code from option 1. Thanks in advance for your advice. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Removing Duplicate Records
Matt, If you went with option #3, you could avoid the looping by using (tCount - 1) as the LIMIT in the delete statement instead of always using 1. Regards, Nathan -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Tuesday, July 14, 2009 8:58 AM To: mysql@lists.mysql.com Subject: Removing Duplicate Records In our database we have an Organizations table and a Contacts table, and a linking table that associates Contacts with Organizations. Occassionally we manually add to the linking table with information gleaned from outside data sources. This is common enough to be annoying, since it ends up with duplicate linkages, but it's FAR from an everyday occurance. I have three options for dealing with the resulting duplicates and I would appreciate some advice on which option might be best. 1. Attack the horrific spaghetti code that determines the Org and Contact ids and then does the manual add. Creating Orgs and Contacts as needed. Calling this code horrific is a kindness... we're talking evil... We've pretty much ruled this out due to the horror... but I mention that I considered it. 2. Do a create table and populate that new table with the results of a select distinct from the old table then swap the tables. 3. Do... SELECT count(*) AS tCount,OrgID,ContID FROM OrgContLink GROUP BY OrgID,ContID HAVING tCount 1. Then for every record in the result DELETE FROM OrgContLink WHERE OrgID=X AND ContID=Y LIMIT 1. Then repeat until no results are found. I like option 2 in so far as it's more... Atomic? One create...select, one swap and its done. But even though it feels more pure I worry that the overhead of completely creating and destroying entire tables seems like throwing the baby out with the bathwater. IOW: Is rebuilding a whole table for a few (hundred at most) offending duplicate records overkill. I like option 3 in that it leaves everything as is but does require a lot of looping and feels inefficient. However, since we'd be running this only after we do our imports it's not like this looping inefficient code would be running all the time. I know I could probably also put a unique key on both orgid and contid but unless I'm reading the docs wrong I can't add a key in such a way that the duplicate key insertion would silently fail without requiring the offending application to do INSERT ... ON DUPLICATE KEY... which gets back to modifying the spaghetti code from option 1. Thanks in advance for your advice. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Removing Duplicate Records
You can combine the two queries you have in option 3 (you'll need to change field names, but you should get the idea), something like this: DELETE table1 FROM table1, (SELECT MAX(id) AS dupid, COUNT(id) AS dupcnt FROM table1 WHERE field1 IS NOT NULL GROUP BY link_id HAVING dupcnt1) AS dups WHERE table1.id=dups.dupid; Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ smime.p7s Description: S/MIME cryptographic signature
Re: Removing Duplicate Records
That's assuming that there is a unique identifier field, like an auto increment field. Although that could be added after the fact. Also, you need to run the query multiple times until it returns no affected records. So if there are 4 copies of a record, it would need to be run 3 times to get rid of all the dups. But I agree, that is the best way to remove duplicates in place provided the table is not too large. Brent Baisley On Tue, Jul 14, 2009 at 11:52 AM, Marcus Bointonmar...@synchromedia.co.uk wrote: You can combine the two queries you have in option 3 (you'll need to change field names, but you should get the idea), something like this: DELETE table1 FROM table1, (SELECT MAX(id) AS dupid, COUNT(id) AS dupcnt FROM table1 WHERE field1 IS NOT NULL GROUP BY link_id HAVING dupcnt1) AS dups WHERE table1.id=dups.dupid; Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Removing Duplicate Records
Ah... Yes. Good point. I like this because I was planning on keeping the output somewhere for a while. (In case we need an accounting at some point) So it will be easy enough to dump what's being deleted to the screen while we loop over our candidates. Thanks! On Tue, Jul 14, 2009 at 10:16 AM, Nathan Sullivannsulli...@cappex.com wrote: If you went with option #3, you could avoid the looping by using (tCount - 1) as the LIMIT in the delete statement instead of always using 1. 3. Do... SELECT count(*) AS tCount,OrgID,ContID FROM OrgContLink GROUP BY OrgID,ContID HAVING tCount 1. Then for every record in the result DELETE FROM OrgContLink WHERE OrgID=X AND ContID=Y LIMIT 1. Then repeat until no results are found. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Deleting Duplicate Records
Hi, I used the following SQL Query to determine Duplicate Records with same Name Email in the row: SELECT email, COUNT(email) AS NumOccur1, name, COUNT(name) AS NumOccur2 FROM mytbl GROUP BY name, email HAVING ( COUNT(email) 1 ) AND ( COUNT(name) 1 ) I want to delete all the records which are duplicate, leaving the original record as it is. How can I delete Only the Duplicate records? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
Hi, It¹s didn¹t work. I think I know why. I have 5 fields in my table. ID, name, email, sex, country. Although in the duplicate rows.. Name, email, sex country values are duplicate, the value in the ID column is different for each. It happened so when people added their entry multiple times. The ID was new each time, however everything else was the same. Your code would probably eliminate all Duplicate rows from a table. But since the ID is different in each of those records, none of those records actually classify as a Duplicate ROW. Any other way? Thanks. On 11/8/05 11:10 AM, Shen139 [EMAIL PROTECTED] wrote: ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( field1, field2,... ) ; On 11/8/05, Rahul S. Johari [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi, I used the following SQL Query to determine Duplicate Records with same Name Email in the row: SELECT email, COUNT(email) AS NumOccur1, name, COUNT(name) AS NumOccur2 FROM mytbl GROUP BY name, email HAVING ( COUNT(email) 1 ) AND ( COUNT(name) 1 ) I want to delete all the records which are duplicate, leaving the original record as it is. How can I delete Only the Duplicate records? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Rahul S. Johari Coordinator, Internet Administration Informed Marketing Services Inc. 251 River Street Troy, NY 12180 Tel: (518) 266-0909 x154 Fax: (518) 266-0909 Email: [EMAIL PROTECTED] http://www.informed-sources.com
Re: Deleting Duplicate Records
That worked Perfect!!! Thanks a ton! On 11/8/05 11:27 AM, Shen139 [EMAIL PROTECTED] wrote: ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( Name, email, sex, country ) ; Rahul S. Johari Coordinator, Internet Administration Informed Marketing Services Inc. 251 River Street Troy, NY 12180 Tel: (518) 266-0909 x154 Fax: (518) 266-0909 Email: [EMAIL PROTECTED] http://www.informed-sources.com
Re: Delete duplicate records
Manish wrote: I have got few duplicate record in my DB as follows.- Let's say record_no is the unique id of duplicate records. So I have fields ID, record_no , X and Y. (ID is a dif filed and is a primary key) To find duplicates I do following - select record_no,count(record_no) from mytable group by record_no having count 1; Now I want to delete all these duplicate records in such a way that only one of them for each record_no stays in the database (the one with highest ID may stay). How can I do this?? TIA, - Manish delete from mytable where record_no=? limit ? Setting the first parameter to the record number and the second to count - 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete duplicate records
delete from mytable where record_no=? limit ? Setting the first parameter to the record number and the second to count - 1. Ah limit, yes that should help. However, I do not know each individual nos for these record_no Can I have sth that will dynamically delete those records when count 1. My version does not allow subqueries, and I am not too familiar with advanced MySQL stuff. TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete duplicate records
I have got few duplicate record in my DB as follows.- Let's say record_no is the unique id of duplicate records. So I have fields ID, record_no , X and Y. (ID is a dif filed and is a primary key) To find duplicates I do following - select record_no,count(record_no) from mytable group by record_no having count 1; Now I want to delete all these duplicate records in such a way that only one of them for each record_no stays in the database (the one with highest ID may stay). How can I do this?? TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there an easy way to find duplicate records in a table?
This query will show you a list of all Vendor, ID combinations, and how many times they appear if they appear more than once in your data: SELECT Vendor, ID, Count(1) as dupes FROM name_of_your_table_goes_here GROUP BY Vendor, ID HAVING Count(1) 1 Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Gannaway [EMAIL PROTECTED]To: [EMAIL PROTECTED] hics.comcc: Fax to: 07/10/2004 05:10 Subject: Is there an easy way to find duplicate records in a table? PM I have a table that our distributor sent us. The table doesn't have any keys. It does, however, have 7,782 duplicate records. I found this out when I tried to have MySQL make a unique product ID by combining 2 fields of each record. Here's what I need to know... Is there a MySQL command that will reveal duplicate entries in a table, and find them by looking at just 2 fields??? I'm trying to create a primary key in the new table by combining 2 fields together (Vendor and ID). Sample data format: +==+ | ImportTable | +==+ | Vendor | ID | Price | ++-+---+ | AAD| 1 | $9.98 | | AAD| 1 | $3.98 | | AAD| 52 | $9.98 | | BCD| 2 | $8.98 | | BCD| 5 | $8.98 | | CSX| 44 | $7.98 | ++-+---+ I'd like something that will identify 'AAD 1' as a duplicate, even though their Prices are different. Thanks!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there an easy way to find duplicate records in a table?
I have a table that our distributor sent us. The table doesn't have any keys. It does, however, have 7,782 duplicate records. I found this out when I tried to have MySQL make a unique product ID by combining 2 fields of each record. Here's what I need to know... Is there a MySQL command that will reveal duplicate entries in a table, and find them by looking at just 2 fields??? I'm trying to create a primary key in the new table by combining 2 fields together (Vendor and ID). Sample data format: +==+ | ImportTable | +==+ | Vendor | ID | Price | ++-+---+ | AAD| 1 | $9.98 | | AAD| 1 | $3.98 | | AAD| 52 | $9.98 | | BCD| 2 | $8.98 | | BCD| 5 | $8.98 | | CSX| 44 | $7.98 | ++-+---+ I'd like something that will identify 'AAD 1' as a duplicate, even though their Prices are different. Thanks!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there an easy way to find duplicate records in a table?
select Vendor, ID, count(*) from ImportTable group by Vendor, ID having count(*) 1; -Original Message- From: Jeff Gannaway [mailto:[EMAIL PROTECTED] Sent: Saturday, July 10, 2004 5:11 PM To: [EMAIL PROTECTED] Subject: Is there an easy way to find duplicate records in a table? I have a table that our distributor sent us. The table doesn't have any keys. It does, however, have 7,782 duplicate records. I found this out when I tried to have MySQL make a unique product ID by combining 2 fields of each record. Here's what I need to know... Is there a MySQL command that will reveal duplicate entries in a table, and find them by looking at just 2 fields??? I'm trying to create a primary key in the new table by combining 2 fields together (Vendor and ID). Sample data format: +==+ | ImportTable | +==+ | Vendor | ID | Price | ++-+---+ | AAD| 1 | $9.98 | | AAD| 1 | $3.98 | | AAD| 52 | $9.98 | | BCD| 2 | $8.98 | | BCD| 5 | $8.98 | | CSX| 44 | $7.98 | ++-+---+ I'd like something that will identify 'AAD 1' as a duplicate, even though their Prices are different. Thanks!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disable/Enable Keys and Duplicate Records
I'm not clear on this. What happens to duplicates under the following scenario? MyISAM table with either (or both!) a primary key or a unique index. Alter table my_table disable keys. Import 200K records. Alter table my_table enable keys. But it turns out there are duplicate records. Does MySQL abort the enable key? Does it drop one of the offending records? If so, which one? MySQL apparently does not have table where offending rows -- the ones deleted -- are stored? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disable/Enable Keys and Duplicate Records
Hi Randy, Unique keys are not disabled with DISABLE KEYS -- so the unique constraint isn't violated. From http://www.mysql.com/doc/en/ALTER_TABLE.html ALTER TABLE ... DISABLE KEYS makes MySQL to stop updating *non-unique* indexes for MyISAM table. Matt - Original Message - From: Randy Chrismon Sent: Monday, October 13, 2003 9:48 AM Subject: Disable/Enable Keys and Duplicate Records I'm not clear on this. What happens to duplicates under the following scenario? MyISAM table with either (or both!) a primary key or a unique index. Alter table my_table disable keys. Import 200K records. Alter table my_table enable keys. But it turns out there are duplicate records. Does MySQL abort the enable key? Does it drop one of the offending records? If so, which one? MySQL apparently does not have table where offending rows -- the ones deleted -- are stored? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting semi-duplicate records
Let's say I have the following table (t1): ++--++ | id | c1 | c2 | ++--++ | 1 | NULL | 1 | | 2 | a| 1 | | 3 | NULL | 2 | | 4 | b| 2 | | 5 | NULL | 3 | | 6 | c| 3 | | 7 | NULL | 4 | ++--++ I would like to delete all rows that c1 = NULL *and* have a duplicate c2. So, in this case I want to eliminate rows 1,3, and 5. So my question is two-fold: 1) What's a good command way to do this? 2) I tried an UPDATE/DELETE combo: UPDATE t1 LEFT JOIN t1 as t2 ON (t1.c2 = t2.c2 AND t2.c1 IS NOT NULL) SET t1.c2 = NULL; DELETE FROM t1 WHERE c1=NULL and c2 =NULL; This would only replace and delete one record at a time (so I would need to run it 3 times). Why is it only updating one row? Why not all matches or no matches? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. CREATE TABLE `new_table` SELECT * FROM `old_table` GROUP BY [your_fields_you_want_unique]; -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
The cleanest way is to add a column (ukey) to the table which will uniquely identify each record. This will help you in the following phases too. Let's say the name of your table is 'focus_table'. 1.adding the unique key 'ukey': ALTER TABLE 'focus_table' ADD COLUMN ukey BIGINT(12) AUTO_INCREMENT PRIMARY KEY; *Now you know what records are remaining after deleting the duplicates and the table will have the same structure. 2.backup your table 3.select the duplicates: CREATE TEMPORARY TABLE dupls SELECT * FROM 'focus_table' GROUP BY field1,field2,... ; *field1,field2,... is the list of the fields which give the duplicates. 4.empty the table: DELETE FROM 'focus_table'; 5.fill it with unique records: INSERT INTO 'focus_table' SELECT * FROM dupls; - Original Message - From: Cybot [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 31, 2003 11:41 AM Subject: Re: Deleting Duplicate Records Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. CREATE TABLE `new_table` SELECT * FROM `old_table` GROUP BY [your_fields_you_want_unique]; -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
To select records only once try something like select * from your_table group by field1, field2, .. Regards, Paul Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
At 02:32 PM 7/30/2003, you wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey Kim, Here are a few solutions on the web: http://www.databasejournal.com/features/mysql/article.php/10897_2201621_3 http://15seconds.com/issue/011009.htm http://www.faqchest.com/prgm/mysql-l/mysql-03/mysql-0302/mysql-030222/mysql03021508_15076.html http://www.experts-exchange.com/Databases/Mysql/Q_20610046.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting Duplicate Records
Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
If all these fields are defined as not NULL, simply use alter table with the ignore option to add a unique key on all pertainant fields. Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
Unfortunately this is not the case. Any other ideas? Kim If all these fields are defined as not NULL, simply use alter table with the ignore option to add a unique key on all pertainant fields. Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
duplicate records check
Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: duplicate records check
Title: RE: duplicate records check Hi Neil, try something like this SELECT your_field FROM your_table GROUP BY your_field HAVING COUNT(*) 1 Mit freundlichen Grssen Frank Kalis Asset Management ProACTIV___ CiV Versicherungen * PB Versicherungen * PB Pensionsfonds AG Neustrae 62, 40721 Hilden tel +49 (0) 21 03-34 - 7282 fax +49 (0) 21 03-34 - 7098 mailto:[EMAIL PROTECTED] internet: www.proactiv.de -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 09, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: duplicate records check Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duplicate records check
Hi Neil, 1) You could ask for the count of each listed item in a column. something like: SELECT item, count(item) from parts GROUP BY item; 2) you could then use SELECT id,item from parts WHERE item = 'xxx'; to review the double entries. This is just a general sample, perhaps if you give more details than we can be more specific. ;-) Best regards Nils Valentin Tokyo/Japan 2003 7 9 15:42Neil Tompkins : Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicate records
Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: ?php $insert_data = INSERT into articles Values ( 'a href=\upload_form.php?id_num=$id_num\edit/a', 'a href=\delete.php?id_num=$id_num\delete/a', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );; $response = mysql_query( $insert_data, $dbh ); $get_table_data = SELECT * FROM ccfs; $response = mysql_query( $get_table_data, $dbh ); ? Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate records
Hello Steve, Do a select on the record before you insert it. If the record does not exist in the database (i.e. mysql_num_rows () == 0) than it is safe to do the insert. John -Original Message- From: Steve Marquez [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 3:41 PM To: MySQL List Cc: PHP eMail List Subject: Duplicate records Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: ?php $insert_data = INSERT into articles Values ( 'a href=\upload_form.php?id_num=$id_num\edit/a', 'a href=\delete.php?id_num=$id_num\delete/a', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );; $response = mysql_query( $insert_data, $dbh ); $get_table_data = SELECT * FROM ccfs; $response = mysql_query( $get_table_data, $dbh ); ? Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate records
You can always catch the Duplicate Key error and respond accordingly in your PHP script. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Steve Marquez [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 1:41 PM To: MySQL List Cc: PHP eMail List Subject: Duplicate records Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: ?php $insert_data = INSERT into articles Values ( 'a href=\upload_form.php?id_num=$id_num\edit/a', 'a href=\delete.php?id_num=$id_num\delete/a', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );; $response = mysql_query( $insert_data, $dbh ); $get_table_data = SELECT * FROM ccfs; $response = mysql_query( $get_table_data, $dbh ); ? Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate records
It is almost safe, but not entirely, since another user could slip in a row with your number in the split second between your Select ... and your Insert. That is the case for blocking the Select ... Insert sequence as one logical unit of work so the target table is locked from Select through Insert. PB - - Original Message - From: John Griffin To: Steve Marquez ; MySQL List Cc: PHP eMail List Sent: Friday, June 06, 2003 2:51 PM Subject: RE: Duplicate records Hello Steve, Do a select on the record before you insert it. If the record does not exist in the database (i.e. mysql_num_rows () == 0) than it is safe to do the insert. John -Original Message- From: Steve Marquez [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 3:41 PM To: MySQL List Cc: PHP eMail List Subject: Duplicate records Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: ?php $insert_data = INSERT into articles Values ( 'a href=\upload_form.php?id_num=$id_num\edit/a', 'a href=\delete.php?id_num=$id_num\delete/a', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );; $response = mysql_query( $insert_data, $dbh ); $get_table_data = SELECT * FROM ccfs; $response = mysql_query( $get_table_data, $dbh ); ? Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Locating Duplicate records always results in Error 127
I am locating 'duplicate entries in a table and updating a status field accordingly. The idea is to locate rows which have a matching 'Name' field, and mark them for subsequent processing. However, my update query *ALWAYS* returns Error 127, even on a newly created table if the matching name field is 8 characters or more???. Below is a minimum script which always results in the error Got error 127 from table handler, with only two records in the table. Change the name fields from (8 chars) to AAA (7 chars) and no error. I would appreciate any help in tracking this down, or a suggestion for another way of detecting duplicate records which doesn't cause the problem. Ian # --- USE test; # # Table structure for table 'error127' # DROP TABLE IF EXISTS `error127`; CREATE TABLE `error127` ( `ItemID` int(11) NOT NULL default '0', `Name` varchar(70) NOT NULL default '', `Status` int(11) NOT NULL default '0', PRIMARY KEY (`ItemID`), KEY `Name` (`Name`) ) TYPE=MyISAM; # # Dumping data for table 'error127' # INSERT INTO `error127` (`ItemID`, `Name`) VALUES(1, ); INSERT INTO `error127` (`ItemID`, `Name`) VALUES(2, ); # # Set status # UPDATE error127 AS a,error127 AS b SET a.status=2 WHERE a.name = b.name AND a.itemid != b.itemid; # --- MySQL 4.011g, WinXP Prof -- Ian Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locating Duplicate records always results in Error 127
Hi! On Mar 17, mySQL list wrote: Below is a minimum script which always results in the error Got error 127 from table handler, with only two records in the table. Change the name fields from (8 chars) to AAA (7 chars) and no error. I tried it on both 4.0.11 and 4.0.12. It, indeed, prodeces error 127 on 4.0.11, but works ok on 4.0.12. Probably, one of the bugfixes fixed this bug as a side effect :) So - just upgrade. 4.0.12 will be out this week. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
preventing duplicate records when using WHERE foo IN syntax
Hello,
I need to get a single result from a SELECT from a table with the following sturcture:
component - varchar(25) primary key
catNum - varchar(25) primary key
price - decimal(10,2)
my component list may contain duplicate catNum ie:
primary drive -WD1GJB
- WD800JB
-none
secondary drive - WD800JB
-none
monitor -SONLCD19
-none
any one catNum will have the same price so when i go looking for parts, knowing the
catNum, i do this:
SELECT price FROM syscomp WHERE catNum = $catNum
no problem - i only look at the first return and am happy!
However when putting together a system - and i have the following:
$system = qq('WD800JB', 'SONLCD19', 'none', 'WD1GJB');
and insert that into my SQL as:
SELECT price FROM syscomp WHERE catNum IN ($system);
if 'WD800JB' is in TWO components, i get two results - thus, and incorrect number of
return results.
(price is doubled)
However when i use:
SELECT DISTINCTROW price FROM syscomp WHERE catNum IN ($system);
and my list looks like this:
$system = qq('WD800JB', 'WD800JB', 'SONLCD19', 'none');
i only get 4 results, the second 'WD800JB' is ignored.
The only solution i can think of is:
$system = ('partA', 'partB', 'partC', 'partD', 'partD');
foreach ($system){
SELECT price FROM syscompt WHERE catNum = '$_'
# incrememnt $total with the first resutl.
}
BUT this seems painfully slow - as this would result in up to 20 calls to the sql
server.
I could ensure that each $system had the matching component, to match on the primary
key, but i don't know how to set up the IN statement for a double part:
WHERE concat (component, '-', catNum) IN ($system)
can anyone help me with a solution.
thanks.
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newby: Duplicate Records in table...
Hi everyone, How do I select a group of records in a table and automatically duplicate them into the same table while modifying 1 or 2 columns without having to re-insert each record individually? Thanks, Doug MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: removing duplicate records
If you use IGNORE in the insert IGNORE into new_table you will get the result you want. -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 3:11 PM To: David Kramer; [EMAIL PROTECTED] Subject: Re: removing duplicate records Thanks David! The entire row, not just one or two columns, is a duplicate which makes life fun.. :-) I can key or unique index only a few columns once the data is cleaned up to prevent this problem. If I create an identical table and include either a key or unique index (innodb seems to like the index better) on all the columns and do a insert into new_table using select * from old_table will mysql quit inserting once a duplicate is hit, or will it keep going and skip over the duplicates? walt On Tuesday 30 July 2002 03:57 pm, David Kramer wrote: You could always use an insert statement into a second table, when performing the insert use a GROUP BY clause to consalidate your records something along these lines but this isnt 100% accurate, I would need the table DDL and business rules behind the DEDUP Insert into tableB ( column names, ... ) (select column a, column b, max(column c), --or you could use MIN from table A group by column a, column b); **JUST make sure your Identifing column, i.e. the column you use to tell which is a duplicate record or not is included in the group by. Also what are the business rules behind the DEDUP(Deduplication)? Are the other values contained in the other columns necessary? If you tell me more about what your trying to do and provide some Table DDL I can help you write this query. Just let me know! Thanks, DK group by statement -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: removing duplicate records Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
removing duplicate records
Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: removing duplicate records
If the columns are defined as not null, just add a unique index on all the fields. alter table ignore add unique(col1.col2.col#); walt wrote: Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: removing duplicate records
You could always use an insert statement into a second table, when performing the insert use a GROUP BY clause to consalidate your records something along these lines but this isnt 100% accurate, I would need the table DDL and business rules behind the DEDUP Insert into tableB ( column names, ... ) (select column a, column b, max(column c), --or you could use MIN from table A group by column a, column b); **JUST make sure your Identifing column, i.e. the column you use to tell which is a duplicate record or not is included in the group by. Also what are the business rules behind the DEDUP(Deduplication)? Are the other values contained in the other columns necessary? If you tell me more about what your trying to do and provide some Table DDL I can help you write this query. Just let me know! Thanks, DK group by statement -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: removing duplicate records Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: removing duplicate records
Thanks David! The entire row, not just one or two columns, is a duplicate which makes life fun.. :-) I can key or unique index only a few columns once the data is cleaned up to prevent this problem. If I create an identical table and include either a key or unique index (innodb seems to like the index better) on all the columns and do a insert into new_table using select * from old_table will mysql quit inserting once a duplicate is hit, or will it keep going and skip over the duplicates? walt On Tuesday 30 July 2002 03:57 pm, David Kramer wrote: You could always use an insert statement into a second table, when performing the insert use a GROUP BY clause to consalidate your records something along these lines but this isnt 100% accurate, I would need the table DDL and business rules behind the DEDUP Insert into tableB ( column names, ... ) (select column a, column b, max(column c), --or you could use MIN from table A group by column a, column b); **JUST make sure your Identifing column, i.e. the column you use to tell which is a duplicate record or not is included in the group by. Also what are the business rules behind the DEDUP(Deduplication)? Are the other values contained in the other columns necessary? If you tell me more about what your trying to do and provide some Table DDL I can help you write this query. Just let me know! Thanks, DK group by statement -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: removing duplicate records Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: removing duplicate records
Walt, Don't trust group by. I am using mysqldump and sort -u on *nix for duplicate. Try both ways, let me know your result. Thanks At 04:11 PM 7/30/2002 -0400, walt wrote: Thanks David! The entire row, not just one or two columns, is a duplicate which makes life fun.. :-) I can key or unique index only a few columns once the data is cleaned up to prevent this problem. If I create an identical table and include either a key or unique index (innodb seems to like the index better) on all the columns and do a insert into new_table using select * from old_table will mysql quit inserting once a duplicate is hit, or will it keep going and skip over the duplicates? walt On Tuesday 30 July 2002 03:57 pm, David Kramer wrote: You could always use an insert statement into a second table, when performing the insert use a GROUP BY clause to consalidate your records something along these lines but this isnt 100% accurate, I would need the table DDL and business rules behind the DEDUP Insert into tableB ( column names, ... ) (select column a, column b, max(column c), --or you could use MIN from table A group by column a, column b); **JUST make sure your Identifing column, i.e. the column you use to tell which is a duplicate record or not is included in the group by. Also what are the business rules behind the DEDUP(Deduplication)? Are the other values contained in the other columns necessary? If you tell me more about what your trying to do and provide some Table DDL I can help you write this query. Just let me know! Thanks, DK group by statement -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: removing duplicate records Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: removing duplicate records
Vivian - Im just curious as to why I shouldnt trust group by? Is there a bug or something within Mysql regarding the group by clause? Walt - you could also create a compound primary key that consists of all columns, then create a unique constraint on the Primary key. I've used this many times in the past, and it works quite well. The only caveat is the SQL will puke when it tries to load the duplicate records into the table, I personally would use the DEDUP process of Insert/select with a group by... But you might want to see what Vivian's response is on the group by issue. DK -Original Message- From: Vivian Wang [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 2:37 PM To: walt; David Kramer; [EMAIL PROTECTED] Subject: Re: removing duplicate records Walt, Don't trust group by. I am using mysqldump and sort -u on *nix for duplicate. Try both ways, let me know your result. Thanks At 04:11 PM 7/30/2002 -0400, walt wrote: Thanks David! The entire row, not just one or two columns, is a duplicate which makes life fun.. :-) I can key or unique index only a few columns once the data is cleaned up to prevent this problem. If I create an identical table and include either a key or unique index (innodb seems to like the index better) on all the columns and do a insert into new_table using select * from old_table will mysql quit inserting once a duplicate is hit, or will it keep going and skip over the duplicates? walt On Tuesday 30 July 2002 03:57 pm, David Kramer wrote: You could always use an insert statement into a second table, when performing the insert use a GROUP BY clause to consalidate your records something along these lines but this isnt 100% accurate, I would need the table DDL and business rules behind the DEDUP Insert into tableB ( column names, ... ) (select column a, column b, max(column c), --or you could use MIN from table A group by column a, column b); **JUST make sure your Identifing column, i.e. the column you use to tell which is a duplicate record or not is included in the group by. Also what are the business rules behind the DEDUP(Deduplication)? Are the other values contained in the other columns necessary? If you tell me more about what your trying to do and provide some Table DDL I can help you write this query. Just let me know! Thanks, DK group by statement -Original Message- From: walt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: removing duplicate records Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to duplicate records
Just a thought on something I was reading with temp tables: 1. extract the records you want to change the date on from your existing table to a temp table. 2. update the date in the temp table 3. insert the new records back into the original table. This avoids the csv extract and changing then reinserting. R.D. --On Thursday, June 20, 2002 11:32:22 AM -0400 Adam Nelson [EMAIL PROTECTED] wrote: I have looked at this problem before and I honestly think there is not a proper way to do it within mysql. The only ways are cut paste, programmatically, or subselects (slated for 4.1?). -Original Message- From: Carlos Fernando Scheidecker Antunes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 2:40 PM To: Don Vu Cc: MySQL List Subject: Re: How to duplicate records Don, Thank you very much. The only problem is that the primary index Code, Model and Year and what I need is to duplicate the Code, Model and Units for a different year, 2003, that does not exist. If I do the statement bellow it won't work. What I need is to insert Code, Model, Units for a specific code match but they have to be duplicated for year 2003. Thanks again, C.F. - Original Message - From: Don Vu [EMAIL PROTECTED] To: Carlos Fernando Scheidecker Antunes [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Tuesday, June 18, 2002 9:38 AM Subject: RE: How to duplicate records mabye an insert into...select will work, something like: INSERT INTO new tablename SELECT Code, Model, Units from original table where Year=2003 and ((Code = 'N200') or (Code='N205')); http://www.mysql.com/doc/I/N/INSERT_SELECT.html -Don -Original Message- From: Carlos Fernando Scheidecker Antunes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 10:25 AM To: MySQL List Subject: How to duplicate records Importance: High Hello all, I've got some records of a Database that I would like to duplicate if possible with an statement. The table has Code, Model, Year, units. The primary key is Code, Model and Year. What I need is to duplicate Code, Model and units for a different year. Say I want to duplicate all records which Code are N200 and N205 with the same model and same units but for year 2003. Is there any select and insert/replace statement to acomplish this? There are about 3.000 records and doing that manually is impossible. I am considering dump the whole file to a .csv and change year to 2003 and then reinsert it. Is there any way to do it in a smart way? Meaning an SQL statement. Thank you, Carlos Fernando. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Richard DeWath System Administrator E.L. and Associates [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to duplicate records
On 20 Jun 2002, at 11:35, Richard DeWath wrote: Just a thought on something I was reading with temp tables: 1. extract the records you want to change the date on from your existing table to a temp table. 2. update the date in the temp table 3. insert the new records back into the original table. This avoids the csv extract and changing then reinserting. You don't need a separate step to update the date. When extracting the records into the temporary table, just change SELECT Code, Model, Units, Year to SELECT Code, Model, Units, 2003 in your SQL. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to duplicate records
Hello all, I've got some records of a Database that I would like to duplicate if possible with an statement. The table has Code, Model, Year, units. The primary key is Code, Model and Year. What I need is to duplicate Code, Model and units for a different year. Say I want to duplicate all records which Code are N200 and N205 with the same model and same units but for year 2003. Is there any select and insert/replace statement to acomplish this? There are about 3.000 records and doing that manually is impossible. I am considering dump the whole file to a .csv and change year to 2003 and then reinsert it. Is there any way to do it in a smart way? Meaning an SQL statement. Thank you, Carlos Fernando. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to duplicate records
Carlos, Tuesday, June 18, 2002, 5:24:48 PM, you wrote: CFSA I've got some records of a Database that I would like to duplicate if CFSA possible with an statement. CFSA The table has Code, Model, Year, units. The primary key is Code, Model and CFSA Year. What I need is to duplicate Code, Model and units for a different CFSA year. CFSA Say I want to duplicate all records which Code are N200 and N205 with the CFSA same model and same units but for year 2003. CFSA Is there any select and insert/replace statement to acomplish this? CFSA There are about 3.000 records and doing that manually is impossible. I am CFSA considering dump the whole file to a .csv and change year to 2003 and then CFSA reinsert it. CFSA Is there any way to do it in a smart way? Meaning an SQL statement. You can't do it with one SQL statement. MySQL has INSERT .. SELECT and REPLACe .. SELECT statements but you can't retrieve data from the same table into which you are inserting. What about inserting necessary data into temporary table and than upload them into your table? You can find info about INSERT .. SELECT and REPLACE .. SELECT at: http://www.mysql.com/doc/I/N/INSERT_SELECT.html http://www.mysql.com/doc/R/E/REPLACE.html CFSA Thank you, CFSA Carlos Fernando. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to duplicate records
mabye an insert into...select will work, something like: INSERT INTO new tablename SELECT Code, Model, Units from original table where Year=2003 and ((Code = 'N200') or (Code='N205')); http://www.mysql.com/doc/I/N/INSERT_SELECT.html -Don -Original Message- From: Carlos Fernando Scheidecker Antunes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 10:25 AM To: MySQL List Subject: How to duplicate records Importance: High Hello all, I've got some records of a Database that I would like to duplicate if possible with an statement. The table has Code, Model, Year, units. The primary key is Code, Model and Year. What I need is to duplicate Code, Model and units for a different year. Say I want to duplicate all records which Code are N200 and N205 with the same model and same units but for year 2003. Is there any select and insert/replace statement to acomplish this? There are about 3.000 records and doing that manually is impossible. I am considering dump the whole file to a .csv and change year to 2003 and then reinsert it. Is there any way to do it in a smart way? Meaning an SQL statement. Thank you, Carlos Fernando. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to duplicate records
Don, Thank you very much. The only problem is that the primary index Code, Model and Year and what I need is to duplicate the Code, Model and Units for a different year, 2003, that does not exist. If I do the statement bellow it won't work. What I need is to insert Code, Model, Units for a specific code match but they have to be duplicated for year 2003. Thanks again, C.F. - Original Message - From: Don Vu [EMAIL PROTECTED] To: Carlos Fernando Scheidecker Antunes [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Tuesday, June 18, 2002 9:38 AM Subject: RE: How to duplicate records mabye an insert into...select will work, something like: INSERT INTO new tablename SELECT Code, Model, Units from original table where Year=2003 and ((Code = 'N200') or (Code='N205')); http://www.mysql.com/doc/I/N/INSERT_SELECT.html -Don -Original Message- From: Carlos Fernando Scheidecker Antunes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 10:25 AM To: MySQL List Subject: How to duplicate records Importance: High Hello all, I've got some records of a Database that I would like to duplicate if possible with an statement. The table has Code, Model, Year, units. The primary key is Code, Model and Year. What I need is to duplicate Code, Model and units for a different year. Say I want to duplicate all records which Code are N200 and N205 with the same model and same units but for year 2003. Is there any select and insert/replace statement to acomplish this? There are about 3.000 records and doing that manually is impossible. I am considering dump the whole file to a .csv and change year to 2003 and then reinsert it. Is there any way to do it in a smart way? Meaning an SQL statement. Thank you, Carlos Fernando. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How do I find duplicate records?
[snip]
Thank you for the email. Following is the php code
but it doesn't work.
?
include (connect_my_db.inc);
connect_my_db();
$result = mysql_query (SELECT DISTINCT id FROM
my_rank WHERE rank='a');
while ($row = mysql_fetch_array($result))
{
$id = substr($row[id], 0, 8);
$query =
SELECT id
FROM my_rank
WHERE id LIKE \$id%\
GROUP BY id
HAVING COUNT(*) 1
;
$result1 = mysql_query($query);
while ($row = mysql_fetch_array($result1))
{
echo $row[id]br;
}
}
?
[/snip]
Try your $query like this...
$query =
SELECT id
FROM my_rank
WHERE id LIKE '$id%'
GROUP BY id
HAVING COUNT(*) 1
;
Single quotes around $id%, and if it is PHP they don't need to be escaped.
After your query, while testing, place this code to catch error reports...
if(!($dbresult = mysql_query($query, $dbconnect))){
print(MySQL reports: . mysql_error() . \n);
exit();
}
You may have to modify the code to place the correct connection variable. If
you can send me one record, and a description of what you are doing, I will
try to help.
Jay Blanchard
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How do I find duplicate records?
Try: select substring(id,1,8) as pfx from foo group by pfx; or, if the column is numeric: select floor(id/100) as pfx from foo group by pfx; Date: Tue, 30 Apr 2002 12:59:05 -0700 (PDT) From: James Dellacova [EMAIL PROTECTED] Subject: How do I find duplicate records? [...] I have over 68,000 records in a table with unique ID field that has 10 chars. Need to create a query to find all matching or duplicate IDs up to 8 chars. (Eg. 12345678%%) [...] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do I find duplicate records?
Hi All, I have over 68,000 records in a table with unique ID field that has 10 chars. Need to create a query to find all matching or duplicate IDs up to 8 chars. (Eg. 12345678%%) Can anyone help me? Thanks in advance for any comments. James __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How do I find duplicate records?
[snip] I have over 68,000 records in a table with unique ID field that has 10 chars. Need to create a query to find all matching or duplicate IDs up to 8 chars. (Eg. 12345678%%) Can anyone help me? [/snip] Try select ID from tblFOO group by ID having count(*) 1 HTH! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How do I find duplicate records?
Thank you for the email. Following is the php code
but it doesn't work.
?
include (connect_my_db.inc);
connect_my_db();
$result = mysql_query (SELECT DISTINCT id FROM
my_rank WHERE rank='a');
while ($row = mysql_fetch_array($result))
{
$id = substr($row[id], 0, 8);
$query =
SELECT id
FROM my_rank
WHERE id LIKE \$id%\
GROUP BY id
HAVING COUNT(*) 1
;
$result1 = mysql_query($query);
while ($row = mysql_fetch_array($result1))
{
echo $row[id]br;
}
}
?
Best regards,
James
--- Jay Blanchard
[EMAIL PROTECTED] wrote:
[snip]
I have over 68,000 records in a table with unique ID
field that has 10 chars. Need to create a query to
find all matching or duplicate IDs up to 8 chars.
(Eg. 12345678%%)
Can anyone help me?
[/snip]
Try
select ID
from tblFOO
group by ID
having count(*) 1
HTH!
Jay Blanchard
Applications Development
nii communications, inc.
210-403-9100 x285
__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need help to find duplicate records.
Dear All,
My record id is 10 char long, all records are unique.
However, many of these ids consist of _. Trying to
print all matching records with first eight characters
of the id. For some weird reason, it's not working
properly. Any comments or help will be greatly
appreciated. Blow is my php code:
?
include (connect_my_db.inc);
connect_my_db();
$result = mysql_query (SELECT id FROM my_rank);
while ($row = mysql_fetch_array($result))
{
$id8 = substr($row[id], 0, 8);
$query =
SELECT id
FROM my_rank
WHERE id LIKE \$id%\
GROUP BY id
HAVING count(*) 1
;
$result1 = mysql_query($query);
while ($row = mysql_fetch_array($result1))
{
echo $row[id]br;
}
}
?
Best regards,
James
__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Deleting duplicate records
That works fine, but I was wondering if there was another way to do it because I receive one text file everyday and the are always duplicate records. We just change to MySQL and I have to load this text file everyday to the database. Thanks again!! Nato -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 4:32 PM To: Natividad Castro; [EMAIL PROTECTED] Subject: RE: Deleting duplicate records At 16:29 -0500 3/4/02, Natividad Castro wrote: Thank you very much!! that was very helpful. By the way, is there any other way to get rid of duplicate records from a text file? What's wrong with the command Jeremy suggested? Thanks again Nato -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 3:00 PM To: Natividad Castro Cc: [EMAIL PROTECTED] Subject: Re: Deleting duplicate records On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote: I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea If the data is in a text file, elimiate the duplicates before MySQL is even involved: cat myfile.txt | sort | uniq nodups.txt Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Deleting duplicate records
Hi to all, I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea Thanks in advanced. Nato - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting duplicate records
On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote: I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea If the data is in a text file, elimiate the duplicates before MySQL is even involved: cat myfile.txt | sort | uniq nodups.txt Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting duplicate records
I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not Why not just filter the data through uniq first? ~~ Ray B. Morris [EMAIL PROTECTED] http://www.webmastersguide.com Copyright 2002 Morris, All rights reserved - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Deleting duplicate records
Thank you very much!! that was very helpful. By the way, is there any other way to get rid of duplicate records from a text file? Thanks again Nato -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 3:00 PM To: Natividad Castro Cc: [EMAIL PROTECTED] Subject: Re: Deleting duplicate records On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote: I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea If the data is in a text file, elimiate the duplicates before MySQL is even involved: cat myfile.txt | sort | uniq nodups.txt Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting duplicate records
On Mon, Mar 04, 2002 at 04:29:16PM -0500, Natividad Castro wrote: Thank you very much!! that was very helpful. By the way, is there any other way to get rid of duplicate records from a text file? I can think of others, but they're not nearly as easy or efficient. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 852,316,080 queries (387/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Duplicate Records
A quick method is to create a new table: CREATE TABLE newtable SELECT DISTINCT * FROM oldtable; note; manually verify newtable has your data. Then proceed: DROP TABLE oldtable; CREATE TABLE oldtable SELECT * FROM newtable; DROP TABLE newtable; -Original Message- From: Rich [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 10, 2002 10:31 AM To: MySql Subject: Duplicate Records How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? TIA Rich -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate Records
any reason not to select distinct into a tmp table? On Sun, 10 Feb 2002, DL Neil wrote: Rich, How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. =Ouch! =Do you have a particular criteria to delete one or other of any duplicate records, or are they absolutely identical (and therefore it doesn't matter which stays/goes)? =You cannot really risk automated deletion in either case! - if the records are slightly different, criteria must be established to determine which is right/should be kept; - if they are absolutely identical, how will you identify in a WHERE clause that only one record of that criteria is to be removed of two identical rows? =To identify the duplicate rows try:- SELECT column(s), COUNT(*) as duplicates FROM tbl GROUP BY key HAVING duplicates 1 =Depending upon the number of rows returned, you could then feed those keys into a series of DELETE ... LIMIT 1 commands - assuming duplicates are strictly 'two of'; otherwise it might be easier to take the listing and do it by hand (using a mgmt package). One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? =ALTER TABLE allows the addition of a new column. The only question is whether to do it before (if it might help the editing job) or after, weeding out the duplicates (to get a more continuous AUTO_INCREMENT sequence, if it's at all of interest). =Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Duplicate Records
How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? TIA Rich -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate Records
Rich, How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. =Ouch! =Do you have a particular criteria to delete one or other of any duplicate records, or are they absolutely identical (and therefore it doesn't matter which stays/goes)? =You cannot really risk automated deletion in either case! - if the records are slightly different, criteria must be established to determine which is right/should be kept; - if they are absolutely identical, how will you identify in a WHERE clause that only one record of that criteria is to be removed of two identical rows? =To identify the duplicate rows try:- SELECT column(s), COUNT(*) as duplicates FROM tbl GROUP BY key HAVING duplicates 1 =Depending upon the number of rows returned, you could then feed those keys into a series of DELETE ... LIMIT 1 commands - assuming duplicates are strictly 'two of'; otherwise it might be easier to take the listing and do it by hand (using a mgmt package). One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? =ALTER TABLE allows the addition of a new column. The only question is whether to do it before (if it might help the editing job) or after, weeding out the duplicates (to get a more continuous AUTO_INCREMENT sequence, if it's at all of interest). =Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate Records
Hi, You can try to use ALTER IGNORE TABLE syntax : ALTER IGNORE TABLE your_table ADD UNIQUE(Id); Regards, Jocelyn Fournier - Original Message - From: Rich [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Sunday, February 10, 2002 5:31 PM Subject: Duplicate Records How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? TIA Rich -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate Records
Dan, [I've cc-ed this to the list and to Rich - who asked the original question] You can do the following to remove duplicates: - create table foo as select distinct cols from table_name_containing_duplicates; - drop table_name_containing_duplicates; - alter table foo rename to table_name_containing_duplicates; Done. Now, make a primay key for the new table!! -- this will avoid duplicates. This is one of a couple of rather neat SQL methods for dealing with duplicates - which strictly-speaking answers Rich's question. What is its disadvantage? The problem with a 'delete' technique is that no real record is kept of the action - which in a commercial situation makes auditors and other 'suits' unhappy. Even in a personal situation it can make life difficult because you don't know what you've got till it's gone! Consequently when it comes to 'deletions', us worry-worts tend to take the longer-winded/more boring approach, eg doing a SELECT before a DELETE, because then one is sure of two things: 1 the actual data being DELETEd (and an audit trail for those that regard such things as 'light reading'); and 2 that there is no 'slight error' in the deletion criteria (or assumptions about the data) which leads to a major 'oops'. A visual inspection row by row (as I recommended) on a table of millions of records would be totally impractical - automated methods would be the only way to go! Regards, =dn Rich, How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. =Ouch! =Do you have a particular criteria to delete one or other of any duplicate records, or are they absolutely identical (and therefore it doesn't matter which stays/goes)? =You cannot really risk automated deletion in either case! - if the records are slightly different, criteria must be established to determine which is right/should be kept; - if they are absolutely identical, how will you identify in a WHERE clause that only one record of that criteria is to be removed of two identical rows? =To identify the duplicate rows try:- SELECT column(s), COUNT(*) as duplicates FROM tbl GROUP BY key HAVING duplicates 1 =Depending upon the number of rows returned, you could then feed those keys into a series of DELETE ... LIMIT 1 commands - assuming duplicates are strictly 'two of'; otherwise it might be easier to take the listing and do it by hand (using a mgmt package). One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? =ALTER TABLE allows the addition of a new column. The only question is whether to do it before (if it might help the editing job) or after, weeding out the duplicates (to get a more continuous AUTO_INCREMENT sequence, if it's at all of interest). =Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate Records
Rich, How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. =Ouch! =Do you have a particular criteria to delete one or other of any duplicate records, or are they absolutely identical (and therefore it doesn't matter which stays/goes)? =You cannot really risk automated deletion in either case! - if the records are slightly different, criteria must be established to determine which is right/should be kept; - if they are absolutely identical, how will you identify in a WHERE clause that only one record of that criteria is to be removed of two identical rows? =To identify the duplicate rows try:- SELECT column(s), COUNT(*) as duplicates FROM tbl GROUP BY key HAVING duplicates 1 =Depending upon the number of rows returned, you could then feed those keys into a series of DELETE ... LIMIT 1 commands - assuming duplicates are strictly 'two of'; otherwise it might be easier to take the listing and do it by hand (using a mgmt package). One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? =ALTER TABLE allows the addition of a new column. The only question is whether to do it before (if it might help the editing job) or after, weeding out the duplicates (to get a more continuous AUTO_INCREMENT sequence, if it's at all of interest). =Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate Records
Hi, You can try to use ALTER IGNORE TABLE syntax : ALTER IGNORE TABLE your_table ADD UNIQUE(Id); Regards, Jocelyn Fournier - Original Message - From: Rich [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Sunday, February 10, 2002 5:31 PM Subject: Duplicate Records How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? TIA Rich -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Duplicate Records
You can do the following to remove duplicates: - create table foo as select distinct cols from table_name_containing_duplicates; - drop table_name_containing_duplicates; - alter table foo rename to table_name_containing_duplicates; Done. Now, make a primay key for the new table!! -- this will avoid duplicates. Regards, Dan -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Monday, 11 February 2002 8:30 a.m. To: Rich; MySql Subject: Re: Duplicate Records Rich, How does one go about removing one of two identical records in a MySQL database? My mistake in an earlier database was not applying a unique number to each record (1, 2, 3, ---). I know it's possible to use the DISTINCT operator to show only one of the identical records in a result, but that does not remove one of them from the database. =Ouch! =Do you have a particular criteria to delete one or other of any duplicate records, or are they absolutely identical (and therefore it doesn't matter which stays/goes)? =You cannot really risk automated deletion in either case! - if the records are slightly different, criteria must be established to determine which is right/should be kept; - if they are absolutely identical, how will you identify in a WHERE clause that only one record of that criteria is to be removed of two identical rows? =To identify the duplicate rows try:- SELECT column(s), COUNT(*) as duplicates FROM tbl GROUP BY key HAVING duplicates 1 =Depending upon the number of rows returned, you could then feed those keys into a series of DELETE ... LIMIT 1 commands - assuming duplicates are strictly 'two of'; otherwise it might be easier to take the listing and do it by hand (using a mgmt package). One thought that I had would be to add a unique number to each record, and that could probably be done manually, one record at a time, but is there a way to automate the process so that unique numbers could be assigned with one command? =ALTER TABLE allows the addition of a new column. The only question is whether to do it before (if it might help the editing job) or after, weeding out the duplicates (to get a more continuous AUTO_INCREMENT sequence, if it's at all of interest). =Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP: Can not append access table with some duplicate records to mysql ver 4.
Does someone know how to append records ignoring records that do not meet the database restrictions (UNIQUE index field). I can do it at the server with the IGNORE clause but that clause that not exist in access. TIA, Dante - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to ignore duplicate records appending from MS-ACCESS.
Hi Everyone, I have just installed mysql 4 on a Linux server I'm comparing it against MS-SQL to see it handles one of our DBs better. The DB has over 8 million records MS-SQL shokes with it. I have a field defined to be UNIQUE and I need to append from another table into it from MS-ACCESS 2000 (using myodbc). My problem is that MS-ACCESS stops when it finds a duplicate record. I need it to IGNORE continue. It does not allow me to insert the keyword IGNORE (it gives me a syntax error) If I do it at the server I have no problem because I have the IGNORE option. Is there a way to formulate the SQL from MS_ACCESS to allow me to ignore duplicate records insert the others? Or is there a switch on mysqld or myodbc that will allow me to do it? Thanks in Advanced, Dante - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
That makes sense, sound like I'm redoing in PHP what the UNIQUE column
already does.
So all I need to do is modify my PHP trap using mysql_affected_rows() =
0, retun a error messagu to the used indicating a rcord already exists.
Will try it, THX!
Pete
Paul DuBois wrote:
At 5:46 PM -0500 6/27/01, Pete Kuczynski wrote:
Thanks Paul!
ps works great!
I modified the database with a UNIQUE column like you recommended
earlier, this will now help in the PHP script to trap the duplicate
entry and advise the user of the dup.
But if hostname now has a UNIQUE index on it, it won't even be possible
for your table to have duplicate hostname values, other than NULL.
Probably all you need to do is just go ahead and try to insert the record,
and if mysql_affected_rows() is 0, it was a duplicate and was rejected.
?
// check for duplicate row
$query = SELECT hostname, COUNT(*) AS count FROM asset
GROUP BY hostname HAVING count 1
$result = mysql_query($query);
// check if row is returned, if yes error, if no insert
if (mysql_num_rows($result) != 0) {
echo error;
} else {
// DO INSERT
}
?
Pete
--
Paul DuBois, [EMAIL PROTECTED]
--
___
Pete Kuczynski
Principal Field Engineer
DHL Airways Inc.
Infrastructure Technology Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select query for duplicate records
Hi, How would a word a select statment, to search a database for duplicate entries in one field. For example, the fields: device, hostname, IP, comments I want to find all instances where there my be two devices with the same hostname. Thanks! Pete -- ___ Pete Kuczynski Principal Field Engineer DHL Airways Inc. Infrastructure Technology Services (773)-462-9758 24/7 Helpdesk 1-800-434-5767 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
Peter, That comes pretty close to the distinct -thread in this mailinglist. You might want to try: SELECT device, count(hostname) FROM your_table GROUP BY device HAVING count(hostname)1; This would display all devices with at least 2 (or more) hostnames. If you always have the same IP address, and the same comments allocated to a certain device, then and only then you can add IP and comments to the SELECT and GROUP BY statement. Vary this as needed or pleased Hope that helps And the manual is great too: http://www.mysql.com/doc/G/r/Group_by_functions.html http://www.mysql.com/doc/S/E/SELECT.html Hannes On 6/27/01 1:13 PM, Pete Kuczynski [EMAIL PROTECTED] wrote: Hi, How would a word a select statment, to search a database for duplicate entries in one field. For example, the fields: device, hostname, IP, comments I want to find all instances where there my be two devices with the same hostname. Thanks! Pete - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
Hi, How would a word a select statment, to search a database for duplicate entries in one field. For example, the fields: device, hostname, IP, comments I want to find all instances where there my be two devices with the same hostname. Thanks! Pete Sir, try the following. SELECT DISTINCT device, hostname FROM table_name GROUP BY hostname HAVING Count(*) 1; Only one of the two or more devices will be listed. If you want to include cases where the same device has been listed twice with the same hostname, leave out the DISTINCT. WOMM (Works On My Machine) Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
Thanks Paul!
ps works great!
I modified the database with a UNIQUE column like you recommended
earlier, this will now help in the PHP script to trap the duplicate
entry and advise the user of the dup.
?
// check for duplicate row
$query = SELECT hostname, COUNT(*) AS count FROM asset
GROUP BY hostname HAVING count 1
$result = mysql_query($query);
// check if row is returned, if yes error, if no insert
if (mysql_num_rows($result) != 0) {
echo error;
} else {
// DO INSERT
}
?
Pete
Paul DuBois wrote:
At 3:13 PM -0500 6/27/01, Pete Kuczynski wrote:
Hi,
How would a word a select statment, to search a database for duplicate
entries in one field.
For example, the fields: device, hostname, IP, comments
I want to find all instances where there my be two devices with the same
hostname.
SELECT hostname, COUNT(*) AS count FROM tbl_name
GROUP BY hostname HAVING count 1
Thanks!
Pete
--
___
Pete Kuczynski
Principal Field Engineer
DHL Airways Inc.
Infrastructure Technology Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767
--
Paul DuBois, [EMAIL PROTECTED]
--
___
Pete Kuczynski
Principal Field Engineer
DHL Airways Inc.
Infrastructure Technology Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
At 5:46 PM -0500 6/27/01, Pete Kuczynski wrote:
Thanks Paul!
ps works great!
I modified the database with a UNIQUE column like you recommended
earlier, this will now help in the PHP script to trap the duplicate
entry and advise the user of the dup.
But if hostname now has a UNIQUE index on it, it won't even be possible
for your table to have duplicate hostname values, other than NULL.
Probably all you need to do is just go ahead and try to insert the record,
and if mysql_affected_rows() is 0, it was a duplicate and was rejected.
?
// check for duplicate row
$query = SELECT hostname, COUNT(*) AS count FROM asset
GROUP BY hostname HAVING count 1
$result = mysql_query($query);
// check if row is returned, if yes error, if no insert
if (mysql_num_rows($result) != 0) {
echo error;
} else {
// DO INSERT
}
?
Pete
--
Paul DuBois, [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Ignoring duplicate records during bulk import
You will need to add a primary key to the column in your table that will uniquely identify a record. The IGNORE keyword only ignores records that would generate a "duplicate key" error if there is already a record in the table with the same key value. So, use ALTER TABLE and add a primary key to one of your columns. "---==[bolMyn]==---" wrote: Well, it seems like nobody wants to bite the question I posted a few days ago about ignoring duplicate records during bulk import. So I thought, that maybe answer was so obvious that I could not miss it. I found the following: "LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name,...)]" The part which I found confusing was: INFILE 'file_name.txt' [REPLACE | IGNORE] because there was 'IGNORE' clause down below. There is no better way to find out the results like testing. So, I exported first a few thousands records from one of my tables and then, imported them back using the above syntax. What I did was: LOAD DATA INFILE '/tmp/myfile.txt' INGORE INTO TABLE mytable If I understand this syntax correctly, MySQL was supposed to skip duplicate records from my import file which were identical to the ones in the table. Well, nothing like that happened. MySQL happily 'imported' all the records, consequently accepting duplicates. My question is: "What gives?" What is a correct syntax to avoid duplicates? MySQL version I'm running is 3.22.32, running on Linux kernel 2.2.18 (RH 6.2 distro). Sent via Deja.com http://www.deja.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
