MYSQL :: Eliminate Duplicate IDs From A Table

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 '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 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 A Table In Database
how can I duplicate a table in my database using phpmyadmin? Do I have to add a table, then the fields then import? or is there an easier way?

Also, if I want to duplicate a database, do I have to go through the same steps as above? create the database, create all the tables then the fields?

How To Duplicate A Table From One Database Into Another, Different Nam
I need the names of the tables to be different, but I want to create an identically structured table in my database patterned after a table in another database. I cant really create it and populate it with select * since I dont know the fields in the other in advance.

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.

Duplicate A Structure Of A Table
I have table A and I need to create table B with the same structure of table A.
Is there a maysql commad to do this?

Duplicate Key Entry On InnoDB Table
I have a table that contains a primary key, designated as VARCHAR (1). I cannot enter the same letter of the alphabet, using lower and upper case, to create 2 entries. For example, 'A', and 'a', both cannot be specified as primary keys.

Removing Duplicate Rows In Table
Any way to remove duplicate rows in a database. I have like 225k rows in one table. Well there must be like 30k in duplicate rows. How would I get rid of them? I thought about using distinct and putting the results in a table then just deleting the other table. Any info?

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?

Deleting Duplicate Entries From Database Table
Suppose a table "address" contains the following records

-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
| mr x | 8th lane | 124364 |
-------------------------------------------------------

Mysqlimport: Error: Duplicate Entry '2' For Key 1, When Using Table: Analysis
I am installing some databases on the mysql databases of my mac osx
10.2.8 but when I do "mysqlimport -u X -p W (directory) *.txt.table" I
am getting this answer:

mysqlimport: Error: Duplicate entry '2' for key 1, when using table:
analysis (analysis: one of the txt.table files). This problem happen
just with one sery of files that I downloaded; any other files of the
same style are having this problem.

Is this clear enough to have a solution to my problem?

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?

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