removing duplicate entries
I have the following two tables ACCOUNTACTION +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AccountActionType | varchar(31) | YES | | NULL| | | DESCRIPTION | varchar(255) | YES | | NULL| | | ACTIONDATE| datetime | YES | | NULL| | | ACCOUNT_ID| bigint(20) | YES | MUL | NULL| | +---+--+--+-+-+---+ and ACCOUNTPAYMENTACTION +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AMOUNTINPENCE | bigint(20) | YES | | NULL| | +---++--+-+-+---+ ACCOUNTPAYMENTACTION shares the primary key with ACCOUNTACTION I need to remove duplicate entries that occured at a specific time in ACCOUNTACTION I then plan to remove the rows in ACCOUNTPAYMENTACTION that are no longer referenced in ACCOUNTACTION by using an outer join I can select the duplicate records in ACCOUNTACTION using select ACCOUNTACTION.ID from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID != min(ACCOUNTACTION.ID)); I am trying to delete these records but am having trouble with the sql delete I tried the following but nothing happened delete ACCOUNTACTION where ACCOUNTACTION.ID in (select ACCOUNTACTION.ID from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID != min(ACCOUNTACTION.ID))); Can anyone help me?
Re: removing duplicate entries
I doubt the belwo sql will give you duplcates select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.IDhttp://accountaction.id/!= min(ACCOUNTACTION.ID http://accountaction.id/)); The reason being, for duplicates records accountaction.id will always equal to min(accountaction.id). try this select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 or select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.IDhttp://accountaction.id/ =min(accountaction.id); I would use the first select statement. On 8/6/08, Magnus Smith [EMAIL PROTECTED] wrote: I have the following two tables ACCOUNTACTION +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AccountActionType | varchar(31) | YES | | NULL| | | DESCRIPTION | varchar(255) | YES | | NULL| | | ACTIONDATE| datetime | YES | | NULL| | | ACCOUNT_ID| bigint(20) | YES | MUL | NULL| | +---+--+--+-+-+---+ and ACCOUNTPAYMENTACTION +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AMOUNTINPENCE | bigint(20) | YES | | NULL| | +---++--+-+-+---+ ACCOUNTPAYMENTACTION shares the primary key with ACCOUNTACTION I need to remove duplicate entries that occured at a specific time in ACCOUNTACTION I then plan to remove the rows in ACCOUNTPAYMENTACTION that are no longer referenced in ACCOUNTACTION by using an outer join I can select the duplicate records in ACCOUNTACTION using select ACCOUNTACTION.ID from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID != min(ACCOUNTACTION.ID)); I am trying to delete these records but am having trouble with the sql delete I tried the following but nothing happened delete ACCOUNTACTION where ACCOUNTACTION.ID in (select ACCOUNTACTION.ID from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID != min(ACCOUNTACTION.ID))); Can anyone help me?
Re: Install Microsoft.Jet
You need the mdac components. free download from MS. Sivasakthi wrote: Hi all, I have tried to import the excel to db , but i get the following error, The OLE DB provider Microsoft.Jet.OLEDB.4.0 has not been registered. how can i install the Microsoft.Jet? System Info: OS Name Microsoft(R) Windows(R) Server 2003, Enterprise Edition for 64-Bit Itanium-based Systems System Type Itanium (TM) -based System Processor ia64 Family 31 Model 1 Stepping 5 GenuineIntel ~1300 Thanks, Siva -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: removing duplicate entries
When I try the first suggestion (i) then I get all the 1682 duplicate rows. The thing is that I need to keep the originals which are the ones with the lowest ACCOUNTACTION.ID value. The second suggestion (ii) gives me 563 rows that are the duplicates with the lowest ACCOUNTACTION.ID which are the ones I wish to keep So the ones I want to delete are the ones in (i) and not (ii) When I use select ACCOUNTACTION.ID from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID != min(ACCOUNTACTION.ID)); then I get 1119 rows which is all the duplicates in (i) less the originals in (ii) The problem I'm having is using this in a delete statement. From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: 06 August 2008 10:11 To: Magnus Smith Cc: mysql@lists.mysql.com Subject: Re: removing duplicate entries I doubt the belwo sql will give you duplcates select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID http://accountaction.id/ != min(ACCOUNTACTION.ID http://accountaction.id/ )); The reason being, for duplicates records accountaction.id will always equal to min(accountaction.id). try this select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 or select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID http://accountaction.id/ =min(accountaction.id); I would use the first select statement. On 8/6/08, Magnus Smith [EMAIL PROTECTED] wrote: I have the following two tables ACCOUNTACTION +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AccountActionType | varchar(31) | YES | | NULL| | | DESCRIPTION | varchar(255) | YES | | NULL| | | ACTIONDATE| datetime | YES | | NULL| | | ACCOUNT_ID| bigint(20) | YES | MUL | NULL| | +---+--+--+-+-+---+ and ACCOUNTPAYMENTACTION +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AMOUNTINPENCE | bigint(20) | YES | | NULL| | +---++--+-+-+---+ ACCOUNTPAYMENTACTION shares the primary key with ACCOUNTACTION I need to remove duplicate entries that occured at a specific time in ACCOUNTACTION I then plan to remove the rows in ACCOUNTPAYMENTACTION that are no longer referenced in ACCOUNTACTION by using an outer join I can select the duplicate records in ACCOUNTACTION using select ACCOUNTACTION.ID from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID != min(ACCOUNTACTION.ID)); I am trying to delete these records but am having trouble with the sql delete I tried the following but nothing happened delete ACCOUNTACTION where ACCOUNTACTION.ID in (select ACCOUNTACTION.ID from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID != min(ACCOUNTACTION.ID))); Can anyone help me?
Picking the better query (join vs subselect)
Hi guys, I have 2 tables cars and parts where car has many parts. I need a query to return some fields from the cars table as well as a field from multiple parts records. I've come to the following approaches, and would like to understand which is the better, and why, or if there's a 3rd and even better approach: Approach 1 - subselects: SELECT cars.id, (SELECT parts.value FROM parts WHERE car_id = cars.id AND id = 3) AS part_3, (SELECT parts.value FROM parts WHERE car_id = cars.id AND id = 4) AS part_4 FROM cars WHERE id = 2; Approach 2 - joins: SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4 FROM cars INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id WHERE parts_3.id = 3 AND parts_4.id = 4 AND cars.id = 2; I've tried to find out if joins are preferred over subselects, but am not able to come to a definite conclusion. I read that correlated subselects are bad, and I should go for the join, but I know the id of the record in the outer query and can hard code that into the subselect (if that makes a difference). The EXPLAIN result only differs in that the select_type is SIMPLE in the JOIN approach, but PRIMARY/DEPENDENT SUBQUERY in the subselect approach. Any tips much appreciated, the full example below. Br, Morten CREATE TABLE cars ( id integer, make varchar(32) ); CREATE TABLE parts ( id integer, car_id integer, value varchar(64) ); INSERT INTO cars (id, make) VALUES (1, 'Ford'); INSERT INTO cars (id, make) VALUES (2, 'Honda'); INSERT INTO parts (id, car_id, value) VALUES (1, 1, 'Wheel'); INSERT INTO parts (id, car_id, value) VALUES (2, 1, 'Tire'); INSERT INTO parts (id, car_id, value) VALUES (3, 2, 'Wheel'); INSERT INTO parts (id, car_id, value) VALUES (4, 2, 'Tire'); INSERT INTO parts (id, car_id, value) VALUES (5, 2, 'Dice'); SELECT cars.id, (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 3) AS part_3, (SELECT parts.value FROM parts WHERE car_id = 2 AND id = 4) AS part_4 FROM cars WHERE id = 2; SELECT cars.id, parts_3.value AS part_3, parts_4.value AS part_4 FROM cars INNER JOIN parts AS parts_3 ON cars.id = parts_3.car_id INNER JOIN parts AS parts_4 ON cars.id = parts_4.car_id WHERE parts_3.id = 3 AND parts_4.id = 4 AND cars.id = 2; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: removing duplicate entries
I just did a test case here select * from amc_25; +--+ | id | +--+ |2 | | 14 | |1 | |2 | +--+ 4 rows in set (0.01 sec) select id from amc_25 group by id having count(id) 1 and id!=min(id); Empty set (0.00 sec) It does not give me any rows. R u sure the rows returned, are the one you want to keep are indeed duplicates On 8/6/08, Magnus Smith [EMAIL PROTECTED] wrote: When I try the first suggestion (i) then I get all the 1682 duplicate rows. The thing is that I need to keep the originals which are the ones with the lowest ACCOUNTACTION.ID http://accountaction.id/ value. The second suggestion (ii) gives me 563 rows that are the duplicates with the lowest ACCOUNTACTION.ID http://accountaction.id/ which are the ones I wish to keep So the ones I want to delete are the ones in (i) and not (ii) When I use select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.ID http://accountaction.id/ != min(ACCOUNTACTION.IDhttp://accountaction.id/ )); then I get 1119 rows which is all the duplicates in (i) less the originals in (ii) The problem I'm having is using this in a delete statement. -- *From:* Ananda Kumar [mailto:[EMAIL PROTECTED] *Sent:* 06 August 2008 10:11 *To:* Magnus Smith *Cc:* mysql@lists.mysql.com *Subject:* Re: removing duplicate entries I doubt the belwo sql will give you duplcates select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.IDhttp://accountaction.id/!= min(ACCOUNTACTION.ID http://accountaction.id/)); The reason being, for duplicates records accountaction.id will always equal to min(accountaction.id). try this select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 or select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.IDhttp://accountaction.id/ =min(accountaction.id); I would use the first select statement. On 8/6/08, Magnus Smith [EMAIL PROTECTED] wrote: I have the following two tables ACCOUNTACTION +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AccountActionType | varchar(31) | YES | | NULL| | | DESCRIPTION | varchar(255) | YES | | NULL| | | ACTIONDATE| datetime | YES | | NULL| | | ACCOUNT_ID| bigint(20) | YES | MUL | NULL| | +---+--+--+-+-+---+ and ACCOUNTPAYMENTACTION +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | ID| bigint(20) | NO | PRI | | | | AMOUNTINPENCE | bigint(20) | YES | | NULL| | +---++--+-+-+---+ ACCOUNTPAYMENTACTION shares the primary key with ACCOUNTACTION I need to remove duplicate entries that occured at a specific time in ACCOUNTACTION I then plan to remove the rows in ACCOUNTPAYMENTACTION that are no longer referenced in ACCOUNTACTION by using an outer join I can select the duplicate records in ACCOUNTACTION using select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.IDhttp://accountaction.id/!= min(ACCOUNTACTION.ID http://accountaction.id/)); I am trying to delete these records but am having trouble with the sql delete I tried the following but nothing happened delete ACCOUNTACTION where ACCOUNTACTION.ID http://accountaction.id/ in (select ACCOUNTACTION.ID http://accountaction.id/ from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) 1 and ACCOUNTACTION.IDhttp://accountaction.id/!= min(ACCOUNTACTION.ID http://accountaction.id/))); Can anyone help me?
Transplanting table structure changes
I've added about a dozen new columns to two tables in my development environment, and now I need to roll it out to our production environment. Can anyone suggest an easy way to do this? I want to avoid typos, as much as I can. I made the original changes with PHPMyAdmin, so I don't have any original script to re-use. The best idea I've come up with is to capture the output of SHOW CREATE TABLE and massage that, but I wonder if I'm missing something. The production environment has data in the old columns, and all of the new columns allow NULL, so all I really need to do is preserve the existing data and add the new columns. Although we've discussed this before, and I admit it makes no sense from the machine's point of view, I want to insert the new fields in a particular place for the benefit of the humans who will be using MS Access to view and filter these tables. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
Data files from 4.1.13 with 5.0.x
Hi, Would data files from 4.1.13 work with 5.0.x or will I have to use an SQL dump? Thanks. -- Richard Heyes http://www.phpguru.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Picking the better query (join vs subselect)
On Wed, Aug 6, 2008 at 5:18 AM, Morten Primdahl [EMAIL PROTECTED] wrote: I've tried to find out if joins are preferred over subselects, but am not able to come to a definite conclusion. I read that correlated subselects are bad, and I should go for the join, but I know the id of the record in the outer query and can hard code that into the subselect (if that makes a difference). Sub queries should be avoided if possible in MySQL. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transplanting table structure changes
Sutput the table definitions and cut and paste the new columns into a ALTER TABLE table_name ADD column_name column-definition statement. --- On Wed, 6/8/08, Jerry Schwartz [EMAIL PROTECTED] wrote: From: Jerry Schwartz [EMAIL PROTECTED] Subject: Transplanting table structure changes To: mysql@lists.mysql.com Date: Wednesday, 6 August, 2008, 4:46 PM I've added about a dozen new columns to two tables in my development environment, and now I need to roll it out to our production environment. Can anyone suggest an easy way to do this? I want to avoid typos, as much as I can. I made the original changes with PHPMyAdmin, so I don't have any original script to re-use. The best idea I've come up with is to capture the output of SHOW CREATE TABLE and massage that, but I wonder if I'm missing something. The production environment has data in the old columns, and all of the new columns allow NULL, so all I really need to do is preserve the existing data and add the new columns. Although we've discussed this before, and I admit it makes no sense from the machine's point of view, I want to insert the new fields in a particular place for the benefit of the humans who will be using MS Access to view and filter these tables. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Picking the better query (join vs subselect)
Out of curiosity, is it generally faster to do a sub query or do it in code for something like this. Schema of Settings table, where the PK is (ApplicationId, SettingId): ApplicationId, SettingId, SettingValue Select SettingValue from Settings where SettingId = 10 and ApplicationId IN (select ApplicationId from Settings where SettingId = 22 and SettingValue = 1); The other solution is to do the two queries separately then do the filtering in code. What's generally faster? Waynn On 8/6/08, Rob Wultsch [EMAIL PROTECTED] wrote: On Wed, Aug 6, 2008 at 5:18 AM, Morten Primdahl [EMAIL PROTECTED] wrote: I've tried to find out if joins are preferred over subselects, but am not able to come to a definite conclusion. I read that correlated subselects are bad, and I should go for the join, but I know the id of the record in the outer query and can hard code that into the subselect (if that makes a difference). Sub queries should be avoided if possible in MySQL. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- 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: Transplanting table structure changes
Hello Jerry, I've added about a dozen new columns to two tables in my development environment, and now I need to roll it out to our production environment. Can anyone suggest an easy way to do this? I want to avoid typos, as much as I can. I made the original changes with PHPMyAdmin, so I don't have any original script to re-use. The best idea I've come up with is to capture the output of SHOW CREATE TABLE and massage that, but I wonder if I'm missing something. You might want to try our Schema Compare tool inside Database Workbench. This tool also has a script recorder that can record any changes you make to (meta)data in order to help you keep your change scripts. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
