MYSQL :: Eliminate Duplicate

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.

Eliminate Duplicate IDs From A Table
I've got a 'books' table with 'id', 'name', 'author' and
'isbn' fields.

Due to an error, my books are duplicated, so I've got (e.g.):
430 - Moby Dick - Herman Melville - 0192833855
430 - Moby Dick - Herman Melville - 0192833855
528 - A Tale of Two Cities - Charles Dickens - 0141439602
528 - A Tale of Two Cities - Charles Dickens - 0141439602

And when trying to find a book by its name, I get two
entries instead of one.

I would like:
1) To set id as "unique id"
2) To temove the duplicates IDs from the table.

Eliminate 'almost Duplicate' Rows
I have a query that works fine but produces a resultset with duplicates. The results are unique in that they have different ID fields, but all the other fields can be identical. I would use DISTINCT to eliminate normal duplicates but I havent got a clue how to go about eliminating rows when all but the ID fields are duplicated.

Eliminate Substring
I'm totally new to MYSQl.
I just would like to know how to remove a sustring "abcdef" which is at the end of all the records of table Ihave.
The records have all a diffrent number of character.
The substring"abcdef" is always is always the last word of the field.

Eliminate LIMIT From Sub Query
I have a single table that tracks all revisions to all issues. The format is like so:

id - auto_increment
issue_id - foreign key to an issue
changed_at - timestamp
assignee - foreign key to a user
change_made - varchar of change that was made at that time.

I need to select the latest change to every issue for one specific user. I would normally do this with:

SELECT * FROM historical_data AS hd WHERE hd.id IN (SELECT id FROM historical_data WHERE hd.id = id ORDER BY changed_at DESC LIMIT 1) AND assignee = 6;

Of course this gives the "You can use LIMIT & IN at the same time" error.

Is there something else I can do to get the same result ... besides moving to Postgres?

Eliminate Rows In A Query (without Many OR)
This is my query :

SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas

There must be a more efficeint way to find what i want than this :

AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598

Something like cas.id is not (list of values)...

Eliminate Dropdown - Set Default Parameter
I want to eliminate a dropdown that allows the user to set the number of days they want their information posted. The maximum allowed is set in the admin panel and the user can select up to that number. I want to be able to retain all rescords unless they are deleted by the Admin. or account user. This is the current dropdown code:

<select name="auction_period">
<?
for($i=1; $i<=$rate["max_period"];$i++)
{
?>
<option value="<? echo $i; ?>"><? echo $i; ?></option>
<?
}
?>
</select>

Duplicate Key
I'm trying to create a new table that will store a name in various languages. When I run the following, I get a "Duplicate entry '1' for key 1 " error.

I understand that it is because the language ID is the same for the first two inserts, but I don't see how to get around that. Can someone please tell me if the table is setup incorrectly or explain what I need to do to get this to work?

DROP TABLE IF EXISTS header;
CREATE TABLE header (
page_name varchar(64) NULL,
language_id int DEFAULT '1' NOT NULL,
PRIMARY KEY (language_id)
) TYPE=MyISAM;

INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('index', '1');
INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('product', '1');
INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('product', '2');

ON DUPLICATE KEY
i have finally got on query working, but it does with "40 rows affected" !! There are only 17 records in my totals table, and in this case only one record gets updated. So two queries would have:

"INSERT IGNORE..." - only 17 to compare.
"UPDATE WHERE..." - only 1 record updated.

INSERT INTO v8totals (tid,tyy,tmm,prices)
SELECT owners,2007,05,SUM(finalprice) FROM v8x200705 GROUP BY owners
ON DUPLICATE KEY UPDATE prices=(SELECT SUM(finalprice) FROM v8x200705 WHERE tid=owners GROUP BY owners);

So either i have written my query badly (and i tried many ways) or this ON DUP UPDATE is very inefficient.

ON DUPLICATE KEY
following common query:

INSERT ...
ON DUPLICATE KEY
UPDATE ...

Is there more to the UPDATE query than just "UPDATE set a = 'b', x = 'y;" ? I am trying to get the UPDATE query to perform a REGEXP comparison on a field in the table before updating it...something like this:

INSERT ...
ON DUPLICATE KEY
IF (x REGEXP 'y' = 1)
THEN
UPDATE ....
ELSE
UPDATE ....
END

Is this sort of thing impossible?

Not Really A Duplicate
I have a table with 3 fields
Directory varchar(10)
Server varchar(10)
File_Name varchar(10) UNIQUE

I set file_name as a primary key because I thought it would be a unique field to ignore all duplicates. below is an example of my data

Directory Server File_Name

abcd svra testfile
abcd svra testfile
abcd svra testfile
efgh svrb testfile

My data is loaded into the database by a comma seperated file.

I perfrom the following command
load data infile 'svrfiles' ignore into table svr_stuff FIELDS TERMINATED BY ',';

it loads the first entry, ignores the rest. I need it to load the 4th entry. removing the ignore statement gives me a dup error.

if I did not ignore duplicates there would be a 1000 rows of data that I do not want to see.

Duplicate Row
I know this might sound nuts, but I want to have a way that the user could duplicate a row. Like an entry they put in, and they want to duplicate it. Does MySQL have something that will just duplicate the entry? Such as if I say taw_id = 5 it will duplicate?

Duplicate Key
I am using mySQL 4.1.6 .
I have a table A with 3 columns A_1, A_2, A_3.
A_1: auto increase field.
A_2: unique name
A_3: description.

Large amount of data will be inserted into this table. And sometimes, it said: Duplicate Key and from that time, I can not insert data until I repair table. I guess there are some problems with the auto-increase field.
Have anybody got experience about this? Could you please share the solution

Duplicate Key Name 'x'
when i upgrade a CMS following error occured .PHP Code:

Invalid SQL:
ALTER TABLE usergroupleader ADD index ugl (userid, usergroupid);

MySQL Error  : Duplicate key name 'ugl'
Error Number : 1061
Date         :
Script       : install/upgrade_302.php?step=1
Referrer     : install/upgrade_302.php
IP Address   : 127.0.0.1
Username     :
Classname    : vB_Database

Duplicate Entry For Key 1
This has me completely stumped, and I've never see anything like it.

Here's my query:

SELECT mus_searchsynonyms.good_term
, mus_searchsynonyms.alt_term
FROM s01_MUS_SearchSynonyms AS mus_searchsynonyms
WHERE mus_searchsynonyms.active = 1
AND mus_searchsynonyms.alt_term = ".quote_smart($search_query)."
AND mus_searchsynonyms.auto_replace = 0
When I use PHP to execute the query, I get the following error message (mysql_error()): (obviously <<$search_query>> is whatever the value of the variable is)

Quote:

Duplicate entry ?-<<$search_query>>' for key 1

When I run the query in phpmyadmin, the query is fine...

First, I don't know why I'd get a key error on a select query, 2nd, there aren't any duplicates in my table...

Duplicate Key Problem
I am quite new to innodb. My problem is a bit funny. I have a table which stores a serial and the status with Id as the primary key which I set. My problem is like this ...

Duplicate Results
I have a DB with a name column. Sometimes, the names might duplicated, so i was wondering how to write a query that says, show me 1 of the records instead of both...?

Duplicate Information
How the simple form that I can run query and drop duplicate information on table.

De-Duplicate SQL Script?
I just put together a PHP mailing list sign-up page that posts to a
mySQL DB. The PHP script prevents INSERTs when the email address is
already located in the database.

Problem: I need to import some flat-files that stored the signups,
prior to this new form. Email addresses weren't checked, so there are
a lot of records that have the same email address.

Once I import these into the table, how would I go about putting
together a SQL statement against that table that would locate
duplicate records (ie. records that share the email address field) and
delete all subsequent ones, but not the first record with that email
address?

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?

Finding Duplicate
select o.operanum from opera_num o
left outer join (
  select p.operanum, count(p.operanum) as c
  from opera_num p
  where to_days(expiry)>to_days(now())
  GROUP by p.operanum
  having count(p.operanum)>1
)as secondUse
on o.operanum = secondUse.operanum
where to_days(o.expiry)>to_days(now());

Update If Duplicate
does anyone know how to do an update on a table only if there's duplicate entry in this table using a query?

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

ON DUPLICATE KEY UPDATE
i have query to set a login time, or update the time if a user is already on the database.
$query = mysql_query("INSERT INTO logged (username,time) VALUES ('$userid','$time') ON DUPLICATE KEY UPDATE time='$time'") or die(mysql_error());

and the erorr i get back is You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE time='1110251002'' at line 1 im running MySQL 4.0.23-standard.

Problem Using ON DUPLICATE KEY
I am trying to use a INSERT INTO SELECT FROM ON DUPLICATE KEY query and having a problem.

The query is as follows;

INSERT INTO product_new (title,description,image_url,buy_link,display_price,sort_price,delivery,merchant_id)
SELECT name,descrip,imageURL,deepLink,CONCAT"Â£",searchPrice),searchPrice,delivery,
(SELECT id FROM merchant WHERE name=advertiser) FROM aw_csv_import
ON DUPLICATE KEY UPDATE description=VALUES(description),display_price=VALUES(display_price),sort_price=VALUES(display_price),date_end=now(),date_updated=now();

The theory behind this is that rows will be inserted into the table but if they already exist then they will be updated as specified in the ON DUPLICATE KEY part.

However I get the following error;

ERROR 1110 (42000): Column 'sort_price' specified twice

I am using Mysql 4.1.8.

Duplicate Rows
I have a table with several rows,some of which I need to duplicate.

The fields of table1 are field1, field2,field3 and field4. Field1 is automatically incremented,
field2 will serve as a filter condition.

Normally to duplicate the rows that matched a certain condition I would do:

INSERT INTO table1 (field2, field3, field4) SELECT field2, field3,field4 WHERE field2=x

This way, I would duplicate the rows that matched the condition and field1 would get it's value automatically incremented by the system.

The catch now is that I want the rows to be duplicated like before,but specifying field4 as "abcd" in all of them.

Duplicate A Table
I want an exact copy of a particular table but under a different name.

Basically I want all of Table 1 duplicated into a new Table 2. How would I do that?

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".

ON DUPLICATE KEY Expansion
i have the following code, and it works fine...
(Primary Keys are v8totals.tid and x200705.owner)

$q="INSERT INTO v8totals (tid,tyy,tmm,finals)
SELECT owner,2007,05,SUM(finalprice) FROM x200705 GROUP BY owner
ON DUPLICATE KEY UPDATE finals=(SELECT SUM(finalprice) FROM x200705 WHERE tid=owner GROUP BY owner)";

But i would like to have another SUM(startprice) in the code...
$q="INSERT INTO v8totals (tid,tyy,tmm,starts,finals)
SELECT owner,2007,05,SUM(startprice),SUM(finalprice) FROM x200705 GROUP BY owner
ON DUPLICATE KEY UPDATE finals=(SELECT SUM(finalprice) FROM x200705 WHERE tid=owner GROUP BY owner)";

How do i expand the ON DUPULICATE bit to update the 'starts' ???

Duplicate Check
I have a table with two columns, T1(id,info). I'd like to add some data from another table which also has two tables T2(id,info).

I have to add data from T2 into T1, but i have to check if the data already exists. Until now i did like this : select info from T1 where info in (select info from T2) but it fails if i have more that 5.000 records. Is there another way to do this ?

Duplicate Key Error
would anyone know what the cause of the following symptom is...

Fatal error: MySQL error: 1062 : Duplicate entry '0-1-1' for key 1 in

why is it not allowed to have multiple entries with same value?

Duplicate Indexes
I have this table:

CREATE TABLE `foo` (
`logId` int(10) unsigned NOT NULL auto_increment,
`uid` varchar(64) default NULL,
`activityDate` datetime default NULL,
`activityType` varchar(255) default NULL,
PRIMARY KEY (`logId`),
UNIQUE KEY `noDups` (`uid`,`activityDate`,`activityType`),
KEY `uid` (`uid`),
KEY `activityType` (`activityType`),
KEY `activityDate` (`activityDate`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8

is the index 'uid' a duplicate index, since it is also the first index in 'noDups' ?

Duplicate Values
today that as a result of some bad code somewhere a huge number of duplicates got inserted such that if I search for the relational id I come up with two of those records with the exact same value.
How could I search through the table to delete all but one of the duplicate records?
For instance, I have the following result set returned when I search for rel_id=45:
id rel_id value
-----------------------
15 45 some text
16 45 some text

Duplicate Entry
I'm currently developing a shopping cart for a local business using php and mysql. I was testing the registration form on the site when all of a sudden I got this error: "Duplicate entry 'tom@thisi.com' for key 1" (that's just a random test email by the way).

I worked out that I only get this error when I use an email that I've used before to signup. Even though I had deleted the previously used email address, it returns an error that it's a duplicate entry. Another strange thing is that the 'email' field is just an ordinary field so a duplicate entry shouldn't cause a problem anyway. If I truncate the table, it's all fixed of course - that's until I use that email address, then delete that account and try and use that email address again.

Source Duplicate
I created a database from a CSV file. Later, I recieve an updated CSV file. The second file contains most of the data from the first file, however some records have been removed and some have been added. Resourcing the database from the new CSV file is not an option because the unique key id column is shared with other tables. So my dilema is, how can I source in the new CSV file and exclude any rows that already exist? Also, how can I identify rows that no longer exist?

I have made a php script that fscanfs my CSV file then runs a seperate query for each row to see if each seperate record is there or not and generates a new sql script for removing and adding records. But, that means 1 query on the database per record, I have around 200,000 records. So this method is not worthwhile, I gave up after an hour of waiting on it.

I also tried using diff, but every time a new record is present it throws off the list and then im removing and adding records back and forth.

Duplicate Columns
I have a join query and in the two tables I'm joining there are two columns with the same name. I need both the values from the columns with the same name...
When I try to output the result with cfml I get an error.
I need the id column values from this query...

SELECT m.*,p.*
FROM messages m, profiles p
WHERE m.main_profile_id = p.id
GROUP BY m.id
ORDER BY m.id DESC

I need both the "id" from m and p.

On Duplicate Update
I have tried to use the INSERT...ON DUPLICATE KEY UPDATE in Coldfusion. After I run the query the database seems to have the correct entries in it but Im getting an error message:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]Duplicate entry '0-thisURL' for key 2
<cfqueryparam value="#now()#" cfsqltype="cf_sql_date">)
v>

Am I way off the mark here? Like I say the entries in the database seem sorrect but I still get the error.

DUPLICATE KEY UPDATE
Like many tables, mine has a PK with AUTO_INCREMENT set. I would like to roll my UPDATE and INSERT sprocs together into one to keep things clean.

I'm having a hard time getting my head around how this 'ON DUPLICATE KEY UPDATE' works without checking for a null PK or -1 or something like that.

In other words, if I'm inserting new data, I don't have the PK value and my table has it's PK column as AUTO_INCREMENT, so how do I handle that? Pass it NULL for the PK on INSERT and a valid PK on UPDATES?

In the past I have done this with an IF ELSE block to check for existence, but the MySql docs lead me to believe this is built in somehow.

On Duplicate Key Update
The table has an autoincrement field that is the primary field. It also has id, date, and course_number that are indexed fields. Since the ON DUPLICATE KEY UPDATE how will I Insert and on duplicate update? The ON DUPLICATE KEY UPDATE works only when the field is the primary field. Will it work if the field(s) are indexed?

Duplicate Entries
I have a database where I would like to pull out the last 5 entries based on time, but the field I am identifying with is a foreign key and therefore, not always unique. How can I make sure I'm getting 5 unique foreign keys in my query

Duplicate Record
I have a table where i have twenty columns each record related to one user
so if i have data already in the tables for User X i want to enter the same data for user Y in the table again. so the only change will be username and the rest is same.
is there any direct way to duplicate data related to one user for another user in mySQL?
or first i have to read the data for user X and then store it in variables and then insert it again for user Y

Duplicate Entry
I have a table with 10 columns and i insert data into it which is unique in a column called (scrape), so to do that i use
Code:

<?
$reb = select count(*) from table where scrape = '$scrape';
if (mysql_result($reb,0) > 0) {
echo "Item Already Added!<br>";
} else {
add the item
}
?> //I know it's a n00bish way

Duplicate Error
I'm getting this error, [MySQL Error Nr. 1025
Error on rename of './epis/bgroups2' to './epis/bgroups' (errno: 121)], when renaming a table with
ALTER TABLE `epis`.`bgroups2` RENAME TO `epis`.`bgroups`;
Someone earlier said it's an error for a duplicate name. I deleted, manually, table bgroups not that long ago and now have only bgroups2. I want to rename it to bgroups back. Can someone help me with this one?
I went as far as deleting entire database so that my tables could be renamed, but getting the same error... The original table names appear to be stored somewhere.

Duplicate Entries
I've got a customer table which contains two email fields...
The first column ( email_off ) is the office email, and the other column ( email_home ), is the home email.
I set up my newsletter script, but I don't want to send it twice (or more) to the same email...
I'd like to get rid of ANY duplicate in those two column.
I made a little php code that looks at the 2nd column to see if the office and home email are the same. There's no problem there.
The problem is, I can't check EVERY client for duplicates while sending the newsletter, because there's like 20000+ customers in the table.
I'd like to run a sql script that updates EVERY duplicates to a blank, but stills keeps ONE record of the email.

Duplicate Entries
i have been inputting data into my database for sometime now and forgot to make one of the fields unique.
i am wanting to make sure that i ahve not put any duplicate ref numbers in this field. is there any query i can run to find out if there are any duplicates without scrolling through the hundreds of entries.

Duplicate Entry
I am with a hosting providing use xcart and i keep tgettin this error message:

Quote: INVALID SQL: 1062 : Duplicate entry '' for key 2
SQL QUERY FAILURE: INSERT INTO xcart_products (provider, add_date)
VALUES ('master', '1109438680')

Eliminate Duplicate

i whould like to know is it's possible to write a sql command to eliminate possible duplicate entries in a table... for example a table with 3 columns i want to eliminate duplicates of column 2.

i whould like to know is it's possible to write a sql command to eliminate possible duplicate entries in a table...
for example a table with 3 columns
i want to eliminate duplicates of column 2.