MYSQL :: Duplicate Records Delete Query

1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network.

I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables.

I've tried following query

DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11;

But it gives ' error in query.

How Do You Delete A Duplicate Row?
I have two rows that have matching data. How can I delete just one of them?

Delete All Duplicate Rows
Please give me the query to remove all the duplicate rows from the table.

DELETE DUPLICATE Except LAST ENTRY?
- HOW TO DELETE DUPLICATE FROM MYSQL EXCEPT LAST ENTRY?

CREATE TABLE `chatone` (
`id` int(255) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`message` varchar(250) NOT NULL default '',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `chatone`
--

INSERT INTO `chatone` VALUES (1, 'test', 'orange', &#55614;&#57159;-03-13 15:25:24');
INSERT INTO `chatone` VALUES (2, 'test', 'orange', &#55614;&#57159;-03-13 15:25:24');
INSERT INTO `chatone` VALUES (3, 'test', 'apple', &#55614;&#57159;-03-13 15:25:48');
INSERT INTO `chatone` VALUES (4, 'donald', 'hello', &#55614;&#57159;-03-13 15:25:48');

Delete Duplicate Rows
How do I delete rows having duplicate data.Of course one row should be there remaining.I was asked to do this with a Delete query and using limit clause.But I am clueless.

Delete Duplicate Data
I am having a problem with deleting duplicate data and keeping one of those records after the delete. I have a table (Emp ID, Employee Name, Address) that contains duplicate data. I want to insert that information into a new table without duplicate data. What I need to do though is still insert one of those duplicate records because it will need to be there eventually. Is there a way to insert only one of the duplicate records into the new table?

Delete Duplicate Entry From A Table
I have a question about delete command: I have to delete duplicate entry from a table and I think I have to use the following statement:

delete from ip_siti_gbw where data_evento in (
select data_evento
from ip_siti_gbw
group by data_evento
having count (data_evento ) > 1
)

When I execute I get an error about count function: FUNCTION statistiche.count does not exist

Can I use count function into a subquery such this?

Delete Records Without Matching Records
I have a one to many relationship in my database. I would like to delete records in one table if there are no matching records in another table.

Duplicate Records
I've noticed what might be a bug. I'm using InnoDB tables to store some payment information. Included in the table is a DATETIME field that logs the exact time a payment was made to a client. The problem is that in one instance there are clearly duplicates of a payment record in the table. i.e. 3 records were inserted that have the exact same information including the same timestamp. I have checked the code making the INSERTs and it can't be that. It's just a simple INSERT query. Has anyone else noticed things like this and is there a solution?

Duplicate Records
mysql> select id, student_first_name, student_last_name, email,
application_date, modification_date, unique_key from student where id
in (7268, 862);
+------+--------------------+-------------------+----------------------------+---------------------+---------------------+------------------+
| id | student_first_name | student_last_name | email
| application_date | modification_date | unique_key |
+------+--------------------+-------------------+----------------------------+---------------------+---------------------+------------------+
| 862 | Phil | Powell | phil@blah.com |
2006-02-27 00:00:00 | 2006-02-27 00:00:00 | dF0WByrCP0vACftA |
| 7268 | Phil | Powell | phil@blah.com |
2006-02-27 00:00:00 | 2006-02-27 00:00:00 | dF0WByrCP0vACftA |
+------+--------------------+-------------------+----------------------------+---------------------+---------------------+------------------+

I accidentally created duplicate records upon attempting to migrate
data from one server to another. You will have 2, 3 or more records
with every single field identical except for the ID. Best way to know
they're dups is by "application_date" along with "unique_key".

Stop Duplicate Records
How do i stop duplicate records from being inserted into the database. i need to stop duplicates of title and link from being entered .....

Eliminate ALL Duplicate Records
I am trying to compare 1 field table 1.Mat_Num and compare it to table 2.Mat_Num
and eliminate all overlap so that only fields in table 1.Mat_Num that are not in table 2.Mat_Num are remaining. Or something like the following query:

Select table1.Mat_Num
From table1.Mat_Num, table2.Mat_Num
Where table1.Mat_Num <> table2.Mat_Num

I have used LIKE, NOT LIKE, IN, NOT IN, JOIN, and everthing else found on google just short of scripting. Obvously, my current results have been a loop listing every instance where table1.Mat_Num(Field X) NOT table2.Mat_Num(Field X). so it is not taking table1.Mat_Num(Field 1) and then searching all fields in table2.Mat_Num for an occurance.

How To Avoid Duplicate Records
I need to filter out duplicates for every 30 seconds. say i have two duplicate records within the 30 seconds limit. I need to show up only one. If there are identical records but with a different time settings(say above 30 seconds) then i need to display it. I need to restrict duplicate records within 30 seconds.

Picking Out Duplicate Records
I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?

ID - StudentNo - Subject - Term - Yearofstudy - YearTaken - Grade
1195 - 11111111 - MA1E2 - Annual - 1 - 2006 - 34
1205 - 11111111 - MA1E2 - Supplemental - 1 - 2006 - 40 (S)
1194 - 11111111 - MA1E1 - Annual -1 - 2006 - 35
1204 - 11111111 - MA1E1 - Supplemental - 1 - 2006 - 40 (S)

Here is the SQL I'm using to get all the records from the studentresults table, for first years only:

SELECT *
FROM studentresults
WHERE studentresults.StudentNo = 11111111 AND studentresults.YearOfStudy = 1

How do I change this to pick out only the supplemental exam results?

Finding Duplicate Records
I'm trying to setup a way to find possible duplicate or near-duplicate
records of contact people. I have a table of 50k records doing an
inner join with itself, something like this:

SELECT p1.fields, p2.fields
FROM table AS p1 INNER JOIN table AS p2 ON p1.lastname = p2.lastname
WHERE p1.id <p2.id AND
(other criteria)

The id and name and other criteria fields are indexed as well as they
can be. It works fine as long as I add a LIMIT statement to keep it
down to just 10 or 20 records, but if I take this off, it runs on
forever -- so I can't even tell how many potentail records there will
be. If the table grows to 100k or 200k, it'll only get worse.

So my question is -- are there any better ways to go about this?

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.

Deleting Duplicate Records
how to delete duplicate records from my DB, where I have about 65502 records. I have a auto-incremend "id" field, then I have a file_letter field (which holds info like A, B, C - Z), and then I have a file_no field. Basically, the file_letter and file_no fields make a unique pair, thus there can be more than one "file_no = '23' (for ex. file_letter = A, file_no = 32 / file_letter=B, file_no = 32, etc etc)

How do I delete duplicate records using these two fields as a unique pair? I'm running MySQL 5.0.15-max-log.

Way To Duplicate Table And Its Records?
Is there a way in mySQL to duplicate a table? I want to copy a table's structure and all of its records, for testing purposes.

Stop Duplicate Records
Sorry if this is extremely newbie of me but how do i stop duplicate records? I have created a small site with php and mysql which allows a user to reserve a parking space online.

Im only learning mysql and php so its all done very simply - I have a table with 2 fields - space and daybooked. users can go online and book for example Space number 2 for Wednesday. How would i carry out a check that would stop another user from double booking the same space?

Removing Duplicate Records
I received a table from a third party that should be primary keyed on a column called item_id. But that column has four rows that are duplicated, including item_id. I would like to remove one of each of the duplicate rows, leaving the other row intact.
MYSQLBrowser won't let me create a primary key until this is solved.

Non Duplicate Records Querry
I have a table with 701 duplicate records. The number of non duplicate records from this table is 179 I tryed to make a querry like :

Select Distinct count(recordname) from table

but the result is the same 701

What is the proper querry to get the 179 result ?

Remove Duplicate Records
What is the best way to remove duplicate records

Stopping Duplicate Records
I've got a table which has three colums. The first is the row id, the other two are basically parent and child.
I need to ensure that there aren't any duplicate parent/child inserts, but without an extra hit to the DB to check first.

Returning Results Without Duplicate Records
I have 2 tables with related data.
one table is for products. and the other orders. In orders table one product may appear several times.
Now if i wanted to return an array with the products being ordered I will get duplicates of each product.
How can I return the results showing the products ordered wirhout containg duplicate rows

Avoid Duplicate Records In Within 30 Seconds
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales.

So for example, I'd display
John Doe All Time Sales Ranking: #138

I'm not exactly sure how to go about this.

$query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC";

This query would give me the data to list all of the Sellers in desc order by the number of sales.

In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.

DISTINCT Still Showing Duplicate Records
Why won't distinct work? The sql IS selecting correctly but its showling records with the same f.preplyid

CODE

SELECT DISTINCT (f.preplyid), f.pid, f.ptid, f.ptid2, f.puid, f.psubject, f.ptype, f.ptime, t1.tid, t1.tname tname1, t2.tid, t2.tname tname2 FROM forumpost as f, thread as t1, thread as t2 WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 AND f.puid =2 AND f.ptype=1 AND f.puid NOT IN (SELECT fp.puid FROM forumpost as fp WHERE fp.ptype=0 AND fp.pid=f.preplyid) ORDER by f.ptime desc LIMIT 7

Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database:

ID Channel Description
41 Africa News Latest news from africa
42 Technology News Latest Tech News
43 PC News Latest PC related news
44 Africa News Latest news from africa

I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique.

How can i return only one of the Africa entries in the results while still having the primary key value returned as well?

Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?

DELETE Records
.I am trying to select a set of similar data in 5 tables and delete rows in another table that do not contain matches to this data. I basically want to clump all 'col1' together (with a join?) in table1,table2,table3,table4,table5, and compare these values to 'col1' in table6. It would be nice to do it in one statement, but I lack the knowledge to pull it off right now.

Here my prelim setup, but I'm stuck!

CODE$query1 = "SELECT table1.col1, table2.col1, table3.col1, table4.col1, table5.col1 FROM table1, table2, table3, table4, table5";

$query2 = "DELETE FROM table6 WHERE (table6.col1 != table1.col1) AND (table6.col1 != table2.col1) AND (table6.col1 != table3.col1) AND (table6.col1 != table4.col1) AND (table6.col1 != table5.col1)";

Delete Records
I'm trying to delete users who have not verified their accounts and that are more than or equal to 6 hours old.

My time data is stored in a timestamp column.

What is the right way to do this? I just cannot find a way to select users that signed up and did not verify and are more than or equal to 6 hours old. I mean, the hours part is hard to do. Even minutes would do.

Delete Records
How do I delete all records in a table that have no related records in another table? I tried this:

delete from t1 where t1.id=t2.id and count(t2.id)=0;

but it says "Unknown table 't2' in where clause". What's wrong?

Delete Corresponding Records
I'm messing around with this query but for some reason it's not working.

DELETE FROM zsmos_comprofiler c, zsmos_users u, zsmos_core_acl_aro a, zsmos_core_acl_groups_aro_map m WHERE c.id=u.id AND c.id=a.value AND
m.aro_id=a.aro_id AND ((DATE_SUB(CURDATE(),INTERVAL 60 DAY) > u.registerDate)&& (u.lastvisitDate='0000-00-00 00:00:00')) AND c.confirmed = '0'

I want to delete corresponding records in 4 tables linked as above - I've tested it as a SELECT query and that works ok - when I change it to a delete though it throws up an error.

How To Delete A Range Of Records
i want to delete a range of emp records from a single query.
i.e from 0003 to 0028

Please help, currently i use a for loop in php. but i though there should be a single line query.

Delete The Records That Occur More Than Once
I have two tables...(the tables contain some duplicate entries)

I need to combine them and then delete the records that occur more than once....this means not leaving any entry for the duplicate...

Is there any simple way to do this?

How To Do Bulk Delete Of Records?
I have MySQL with Phpmyadmin front-end on a Centos machine.

I have a table with with two fields - Member_id & member_name. Both fields are unique.

Now, the table has over 100,000 records and I have to manually remove about 5000 members from one table and add them to another similar table. I have the list of members to be deleted - member_name in a text file, But Phpmyadmin allows only searching one-by-one. How do I do a bulk delete of all these 5000 members?

Delete Certain Word In All Records ?
I have a table in my database called jos_content (joomla website), which contains all news articles from my site. Each newsarticle has a exclamation-mark in the title (its allready in the rssfeed). I want to delete this exclamation-mark from each newsarticle automatically. Is there any SQL command to do this ? If so, can i put the command in a php file and run a cron job every 10 minutes or so?

Delete Related Records
I have a typical setup with a master table that has a related table with many
records per master record. Since MySql does not support sub-selects, how do I
delete just the set of records I want?

TABLE1 - Master
id, name

TABLE2 - Tran
id,mast_id,item,price

Tables are related via (TABLE1.id = TABLE2.mast_id)

I wish I could do a:
DELETE FROM table2 where mast_id
IN (SELECT id FROM table1 WHERE name = 'fred');

If I'm only deleting one master record, then it is easy, one select to find
the master.id then another to delete all table2 records with that mast_id.
But, the problem I'm having is when I want to delete a batch of records from
table1, I also want to get rid of all of the related records so I do not have
any orphans.

Update A Record Out Of 2 Duplicate Records Which Dont Have Primary Key In Mysql Table
how to update a record out of 2 duplicate records which dont have primary key in mysql table?

Delete 100,000 Records.Lock Issues
I want to delete every hour more than 100,000 records (there is on delete cascade on the reference tables).

I decide to delete them in limit of 40000 (is it a good decision ?)

I heard that i need to worry about locking (there is no select on this records).

Find & Delete Orphan Records
Have 2 tables
ts_sheets :
TimeSheet_ID
TimeSheet_Date
Emp_ID
Orphan

LineItem
TimeSheet_ID
Client
JobNumber etc

The following sql will show me all the ts_sheets records with
no ts_lineitem records

select s.* , l.timesheet_id
from ts_sheets s
left join ts_lineitem l on s.timesheet_id = l.timesheet_id
where l.timesheet_id is null

I would like to delete these orphan records
Something like

UPDATE `ts_sheets` SET `Orhan` = 'Yes'
left join ts_lineitem l on s.timesheet_id = l.timesheet_id
where l.timesheet_id is null

Than delete `Orhan` = 'Yes'

I can not work the syntax out

Unable To Add/edit/delete Records
I have a mysql db - 4 tables - all basicly the same. Three tables permit me to add, edit and delete records from Access via ODBC and one will not. - I am no expert at all this and need some pointers as to where to start looking for the problem. The tables all worked fine before they were recently moved to a new server/host. I have asked this question before and had no replies at all. I really would at least like someone to point me towards somewhere I could get help even if this is not the right place.

How To Cascade Delete Related Records Without Deleting The Main Record?
Let's say I have the following database tables:

CREATE TABLE user
(
id INT UNSIGNED NOT NULL PRIMARY KEY,
username VARCHAR(20) NOT NULL,
) ENGINE = INNODB;

CREATE TABLE user_email
(
user_id INT UNSIGNED NOT NULL PRIMARY KEY,
email VARCHAR(100) NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE user_phone
(
user_id INT UNSIGNED NOT NULL PRIMARY KEY,
phone VARCHAR(100) NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE = INNODB;

What I want to do is delete all records in the 'user_email' and 'user_phone' tables without deleting the record in the user table. I have a database without nearly 20 tables with one-to-one and one-to-many relationships with the user record and I need a way to cascade delete records in relationship tables.

ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

Query Help To Remove Duplicate Data
I have a query that returns: first name, last name, comments.

I would like the result to be something like:

Dan | Hudson | "here is a comment"
| | "here is another comment"
Lisa | Hudson | "my comment"
but currently the name is repeated every time there is a comment. Is there a way to do a group by clause or something that will remove the duplicates?

Dan

Query That Duplicate Row And Increment Date
I have a table EVENT
id date duration
1 2006-01-01 3
2 2006-04-23 5
3 2006-10-01 0

is possible made a query that do it?

id date
1 2006-01-01
1 2006-01-02
1 2006-01-03
1 2006-01-04
2 2006-04-23
2 2006-04-24
2 2006-04-25
2 2006-04-26
2 2006-04-27
2 2006-04-28
3 2006-10-01

URGENT: Please Help Me With My ON DUPLICATE KEY UPDATE Query
Could you please help me do this quickly.

I have a query:

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=a")

What I need it to do is that whne title and brand matches, it is then a duplicate listing. I have set up the unique key for this. When a duplicate listing is found it will then change the groupname value from "1" and update it with "a".

When it then finds the next duplicate listing that has the same title and brand as the duplicate listing that had it's groupname changes to "a", I need it to be change to "b" this time.

So I now have two listings that have a groupname of "a" and "b".

Now when there is more duplicate listings that have the same title and brand as "a" and "b", I now need the groupname to be updated to the actual listings id and not use "a", "b", "c", etc...

How can I do that.

I know how I can do the first one so that it updates to 8, but then how would I do the 2nd and 3rd ones.

Please help, I have to do this urgent as my database is really slow and it is damaging my site as it is taking about a minute or so to do each query.

Also, I would prefer it if it could be done in one single query. I can also use variables in the query and if statements plus other php like

Quote:

mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=$groupname")

Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times.

I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.

Duplicate Records Delete Query

I have a table with duplicate records but the key of the duplicate records is different, that is: The key of one of the record has the format: Rssss The key of the second record has the format: ssss How can I delete both records from the table?

I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?