MYSQL :: Query Help To Remove Duplicate Data

Remove Duplicate Rows - MySQL4 JOIN
I am looking to remove duplicate rows from a table.
I have limited knowledge of MySQL - so please bear with me.
The following code correctly displays the duplicate rows:

SELECT firmname, address1, custom_2, MIN( selector ) AS min_sel, count( * ) AS issimilar
FROM my_table
GROUP BY firmname, address1, custom_2
HAVING issimilar > 1;
Now I try to run an inner join to show the rows, so that I can then delete them (delete is not included yet)

select bad_rows.*
from my_table as bad_rows
inner join (

SELECT firmname,address1,custom_2,MIN(selector) AS min_sel,count(*) AS issimilar
FROM my_table
GROUP BY firmname,address1,custom_2
HAVING issimilar > 1

) as good_rows on good_rows.firmname = bad_rows.firmname
AND good_rows.address1 = bad_rows.address1
AND good_rows.custom_2 = bad_rows.custom_2
AND good_rows.min_sel <> bad_rows.selector;
The problem is that I can't use a select inside a JOIN in MySQL4.

I don't know enough about MySQL to rewite the above so that it will work in MySQL4 - I know its something like:

SELECT t1.firmname, t2.firmname, count(*) AS issimilar
FROM my_table AS t1 INNER JOIN pmd_listings AS t2
ON t1.firmname = t2.firmname
GROUP BY t1.firmname, t2.firmname
HAVING issimilar > 0
ORDER BY issimilar DESC;
Hoping someone can help. Also have a question on comparing data from other tables - but I need the above to work first.

Duplicate Yet Diferent Data In Sql Query
Is there a single SQL Query that will do the following?

mysql> SELECT * FROM user;
+----+----------+
| id | username |
+----+----------+
| 1 | Bill |
| 2 | Bob |
| 3 | Hank |
| 4 | Jim |
+----+----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM email;
+----+-------+------+---------------------------------------+
| id | _from | _to | message |
+----+-------+------+---------------------------------------+
| 1 | 1 | 2 | Blah, blah, blah |
| 2 | 1 | 3 | I told Bob to Blah, Blah, Blah |
| 3 | 3 | 4 | Bill told Bob to Blah, Blah, Blah |
| 4 | 4 | 2 | Did Bill tell you to Blah, Blah, Blah |
+----+-------+------+---------------------------------------+
4 rows in set (0.00 sec)
What SQL Query would result in the following...???
mysql> SELECT ??????????????????????????????;
+----+-------+------+---------------------------------------+
| id | _from | _to | message |
+----+-------+------+---------------------------------------+
| 1 | Bill | Bob | Blah, blah, blah |
| 2 | Bill | Hank | I told Bob to Blah, Blah, Blah |
| 3 | Hank | Jim | Bill told Bob to Blah, Blah, Blah |
| 4 | Jim | Bob | Did Bill tell you to Blah, Blah, Blah |
+----+-------+------+---------------------------------------+
4 rows in set (0.00 sec)

Preventing Duplicate Data
I have an html form that I use in conunction with php. The php validates my form fields for proper format before being inserted into mysql. However if i return back to form after submission and submit the exact same data i get an exact duplicate record only differentiated by my primary key field. How can I ensure that I don't get duplicate records in mysql?

Exclude Duplicate Data
Hello MySQL Gurus -

I have 3 tables:

all_customers, opt_out_customers, and opt_in_customers

Let's say there's an id column and an email_address column in each.

I'm sending a newsletter - I want to send it to:

all_customers except where opt_out or opt_in
opt_in except where in all_customers (so as not to send it twice)

I have to use MySQL 4.0. (no sub-selects, etc.)

I had previously tried with a 'LEFT JOIN' but it only seemed to work to
exclude one or the other (e.g. where NULL). Might a 'UNION' work here?

How To Duplicate Column Data
I have a table. Mulitple columns. The two I'm concerned with are product values (prices). The first is the cover price for a magazine. The second is the purchase price for each issue. I want the purchase price to automatically match the cover price. This way, if I actually paid more for a particular issue, I only have to edit that one entry. Otherwise, the purchase price will always reflect the cover price as I input each new line.

Check For Duplicate Data
I am having a problem sorting duplicate data within a table. I have a table called Employee that contains EmployeeID, EmployeeName, PhoneNumber. This table contains duplicate data. I now have a problem with 2 different users with the same EmployeeID 'abc'.

I wondered if there is a way to write a select statment to delete one of the users with same id. I have tried doing a few things with DISTINCT but I could not get the EmployeeID and 2 EmployeeNames to filter out.

Duplicate Mysql Data
I have a mysql db with three columns: id, fruit, rating

'id' and 'fruit' have been created before, the id automatically increments and fruit has a different value for each row. The 'rating' column has only just been added to the db, as such all rows of that are empty.

I want to run an sql query in phpmyadmin which copies over whatever value is in 'id' over to 'rating', so 'id' and 'rating' carry the same information.

Duplicate Column Data
Here is the scenario

Inventory A was received on 10/2/2004
Inventory A was again received on 10/28/2004

For both records I retreive the same information however when doing so the only unique record on the information line will be the quantity distributed of Inventory A and the price that was distributed for Inventory A. I only want to show it once not duplicate. It would be a hideous task for someone to do before presenting this report. Can anyone please steer me in the right direction?
I have tried using distinct but I assume it will not work because of the two seperate dates a record has.

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?

Extracting Duplicate Data
i have a very simple query.Code:

$gethotelcity = "SELECT hotel_city FROM hotels";

but if London is listed 2 times in the db it understandably comes out twice in the results...is there a clever mysql trick to prevent this?

How To Remove Multiple Spaces From A "data" Field?
I need to remove multiple spaces from random pieces of text in a data field. Can someone tell me how to do this? I've tried using this but it doesn't do anything:

update posts set body = replace(body,' href',' href');
*Note the double spaces before the first href and the single space before the second href.

Merging Two Tables With Some Duplicate Data
I have written a perl script that outputs a SQL script. I need to modify the outputted SQL so that if there is a duplicate Company Name, their info dumped somewhere for an audit.

One of my current dumped lines looks like this:

INSERT INTO companies (Company, Profession, Town, County, Postcode, Country, Telephone, Fax, `Email Address`, Website, `Rep Override`) VALUES ('!!!! TEST BUILDER', 'Builder', 'b', '', '', 'England', '01234 555666', '01234 666777', 'me@here.com', '', 'John Smith');

JOIN Returns Duplicate Data
I am querying from 2 tables asuch:

t1 = property listings
t2 = property photos

SELECT t1.id, t1.area, t1.address, t1.price, t1.bedrooms, t1.bathrooms, t2.fileid
FROM listings AS t1, album AS t2 WHERE t1.id = t2.propid AND status LIKE 'for sale';

I am getting duplicate records when multiple photos are present, eg:

+-----+----------+-------------------------------------+
| id | price | fileid |
+---+----------+-------------------------------------+
| 1 | 899000 | 4490082059794440261166380 |
| 2 | 995000 | 1072603818590874471784367 |
| 3 | 630000 | 1192880275852882142198181 |
| 3 | 630000 | 7498054893972972542204555 |
+-----+----------+-------------------------------------+

LOAD DATA INFILE Or ON DUPLICATE KEY UPDATE
I want to use LOAD DATA INFILE to load a text file into my database because of the speed at which it can complete the import.

But I don't think I can use it so I want to check here first before I completely nix the idea of using it.

Is it possible, using LOAD DATA INFILE and possibly the REPLACE argument, to retain values from specific columns in the row that it's updating. It says in the manual that "You cannot refer to values from the current row and use them in the new row" but goes on to say something about using SET that I don't understand.

Is there another way to use LOAD DATA INFILE and to retain values from specific columns that aren't being updated by a field in the text file?

(i hope some of that makes a little sense?)

If it's impossible to use LOAD DATA INFILE, how can I speed up my ON DUPLICATE KEY UPDATE query. Right now, it just processes 1 row of the pipe delimited text file at a time:

Removing Duplicate Data And Formating Results
I have several queries each returning results with duplicated data, such as this:

staff_id      date           hours        duty

1             1/1/04           8-5            C
2             1/1/04           9-6            W

1             2/1/04           9-4            C
2             2/1/04           8-5            W
3             2/1/04           8-5            C

And so on for each week day.

The tables are as follows:

rosters (rid, wknum, startdate,enddate)
date (dateid, date)
staffday (dateid, rosterid, staffid, hours, duty)
staffcontract (staffid, stafftype)
staff (staffid, name)

What I want at the end on screen is:

ID   DATE1  DATE2........ DATE6    DATE7

1     8-5       9-4              8-3         9-6
2     9-6       8-5              7-2         6-2
3     OFF      8-5              8-3         9-6

My question is how do I group/order/sort the results in mysql so that i don'
t have duplication in the returned data as above. I want to avoid complicat
ed if/then/else in my php when building the output table.

Duplicate Existing Database (innodb), Structure Only, Without Data
There's a database (innodb) named "test1". I want to create a new database with exact the same strcuture, but since there are many tables in this db i do not want to retype the whole commands.

I'm looking for a possibility to duplicate this database, any ideas / tools?

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

Ignore Duplicate Query Items
This formcode retrieves faxes from a DB but some are the same and I do not want those in my list... how do I tell it to ignore duplicates?PHP Code:

$sql = "select c.fax as fax ".
    "from #__comprofiler as c ".
    "left join #__users as u on c.user_id = u.id ".
           " where  (c.user_id = u.id) and (u.block =0) and (c.fax != '') and (u.usertype != 'Super Administrator') and (u.name != 'guest') ".
       " order by c.fax";

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

Remove A ¶
After importing a few thousand lines of data into a MySQL DB, one of my fields has ALOT of data with a ¶ after it.It is causing some problems, and I'd like to know what select statement I could use to ignore/remove it.

Add,Remove,
I am very new to SQL, PHP and all that. I am using MySQL 3.23.58 on my website. I would like to add, remove and display data on a webpage without using any tools or whatever

Remove A Password
I want to remove the password used to acces mysql.

Any idea how to do this?

I tried with mysqladmin -p, but this doesn't work.

Remove Duplicates
I have 3 fields: id, rating, and ip

sample format:

id | rating | ip
1 | 10 | 1.2.3.4
1 | 10 | 1.2.3.4
1 | 10 | 1.2.3.4
1 | 10 | 1.2.3.4
1 | 10 | 1.3.2.4
1 | 10 | 1.1.2.2

I need a query to remove all duplicate ips for rating 10 for id 1 so the above sample will end up like this:

id | rating | ip
1 | 10 | 1.2.3.4
1 | 10 | 1.3.2.4
1 | 10 | 1.1.2.2

please note that id ranges from 1-1000 and rating ranges anywhere from 1 to 10 so I need a query to remove dupe ip's from ratings 1-10 for each id

Remove Field
How can I remove a field on a table, eihter in SQL or PHP ?

Remove From String
I'm looking for some way to remove a part of a string from a table.

I have a table with a field called id. It contains data like this:
;1;6;9;34;54

What i want to do is to be able to remove ie. ";34" from this data, kind of oppisit of CONCAT. Hope that I explain myself well enough.

Do I have to extract all data, remove the substring and save the data in the db again, or is there an easier way?

How To Remove GRANT
After running following command;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO
'vmailuser'@'localhost' IDENTIFIED BY 'password123';
Query OK, 0 rows affected (0.00 sec)

I found I made a mistake to run it. Please advise how to remove GRANT given to vmailuser to test database.

Remove User But Still Can Log In
I use the command to create a user and grant all privileges to this user,
then I delete this user from the user table but I can log in use this user.
Why ?

grant all privileges on *.* to john@"%" identified by 'john'

I then delete the user 'john' from the user table in mysql database.
But then I tried to log in using this user successfully.

How To Remove SQL 3.X And Then Install SQL 4.X?
I have downloaded and installed Redhat Linux Fedora 3 which comes with MySql 3.23.58.13 pre-installed. I would like to upgrade this to the latest version of MySql. Having read the documentation section it says that i need to completely remove the old version of MySQL before i attempt to install the new version. Since I am new to linux and MySql does anyone know how i can remove it in full and then get started with an installation of the latest version?

Remove '' From Fields
I imported data from a CSV file and all the fields end with a symbol thats equivalent to '
' so how do i remove it from my fields.Because of these i cant even use queries select * from country where Name = 'Belgium' doesnt work

but when i do this
select * from country where Name = 'Belgium
' works fine.

Remove My Table
I Accidently remove my Table i think using my Navicat . Is there a way to have table fixed ?==== It said 1049 (Unknow Database) (Null)

Remove Records
There are 2 tables, players and teams. A bunch of the teams were removed from the database by someone else (most of them were invalid records).However there are players still in the database who have a teamID column which refers to the ID of a team that doesn't exist anymore. I want to make a SQL query that says something like

"REMOVE FROM players WHERE TeamID DOES NOT EXIST IN TABLE teams"

I'm sure theres a way to do this, but I can't seem to figure it out, can anyone give me some pointers?

Remove After Six Months
Hey, newbie/noobe/noobie <- ? here
Okay, I have a simple table:PHP Code:

id -> mediumint(8)
date
fullname -> varchar(60)
email -> varchar(64)
emailonly -> enum('Y', 'N')
phone -> varchar(10)

When an entry is inserted, I am putting the date in the following format: 2005-05-13
I am now trying to run a script once a week to remove any entries that are older than 6 months. I am very new to mysql and am getting confused on how to do this, because there seems to be so many different date functions in the mysql documentation. The book I have is a Visual Quickstart to MySQL and isn't going into enough detail...

Remove Numbers
I got a post_title field and some entries have numbers before it.
Example: 3232243 how to eat food. I'd like to find away to remove the numbers from the beginning.

Remove Rows
My table has a column that has some values duplicated. The other values in each row are not identical, but the text in this column is.What I need to do is remove all but one row (doesn't matter which one) in which the value for this column exists in another row.

NAME AGE MEMBER
bob 21 yes
james 25 no
bob 27 yes
sue 12 no

I need to remove all but one row for each name, doesn't matter which one.
I'm not good with MySQL other than simple queries - can someone provide a good complete query for this? Can move it into another table or however is easiest.

Remove With Replace
I have a problem with apostrophes. I accidentally imported a ton of data that included apostrophes and need to remove them from my database (they are messing things up when the info comes back out again).

I have tried these commands,

update `st_galleries` set `text_description` = replace (text_description,"'","");

update `st_galleries` set `text_description` = replace (text_description,char(39),"");

update `st_galleries` set `text_description` = replace (text_description,"char(39)","");

And each time mysql does not seem to see the ' character and comes back with all matches but no changes. By all matches I mean it comes back with 5641 matches (I don't have that many apostrophes, I do have that many rows).

Remove The Subselects
i'm using mysql 4.0.15 which don't support the subselects;
my querry is :
Code:

$query_RecordsetRecherche = "SELECT * FROM Videos WHERE (titre LIKE '%$Search%' OR description LIKE '%$Search%' OR idCoiffeur IN (SELECT idCoiffeur FROM Coiffeurs WHERE prenom LIKE '%$Search%' OR nom LIKE '%$Search%' OR adresse LIKE '%$Search%' OR pays LIKE '%$Search%' OR gouvernora LIKE '%$Search%' OR salon LIKE '%$Search%'))";
$RecordsetRecherche = mysql_query($query_RecordsetRecherche, $DB_Coiffure_TV) or die(mysql_error());
$row_RecordsetRecherche = mysql_fetch_assoc($RecordsetRecherche);
$totalRows_RecordsetRecherche = mysql_num_rows($RecordsetRecherche);

how can i change this search and keep its functionality means how i change this to a querry without subselects, and keep searching the same thing.

Copy And Remove Duplicates
I have a table that I need to add a lot of new data into. But there are a couple of problems with that.....

First, the new data is not completely compatible with the old so I need to copy data from one column to another, if the second column is blank. I also need to make sure that there are no duplicate entries.

I am using MySQL version 5 and I am not real sure how to proceed.

Remove The Same Prefix From Every Table Name In Same Db
I have one DB with many tables, all with the same prefix (abc_) on the table name.

The SQL for renaming one table to another is:
RENAME abc_table1 TO table1;

I wish to remove the prefixes for every table in one swoop, rather than rename individually ;-)

Current table names:
abc_table1
abc_table2
abc_table3

Desired table names:
table1
table2
table3

Special Remove Duplicates
I have a query that returns a result set that is composed of events with a start datetime and an end datetime. Some of these events occur during another event. For example:

User AAA Event 1 Start Datetime 2006-11-01 12:00:00 End Datetime 2006-11-07 18:43:00
User AAA Event 2 Start Datetime 2006-11-03 08:21:00 End Datetime 2006-11-05 19:32:00

I am trying to get a result set that does not include those events that occur during another event. Or for the example above I want Event 1, but not Event 2. I am looking for a little direction.

Remove Strange Characters ('~!@#`)
is there anyway to remove those characters , '~`# when inserting into the database?

Can't Remove MySQL Service On Win XP
I am trying to reinstall MySQL on a Win XP pro box. After
uninstalling MySQL 3.x, a 'MySQL' service is left in the
list of services. I can't figure out how to delete it, and
installation of MySQL 4.1 fails during configuration. The
old service may not be the problem, but can anyone suggest
how to get rid of it?

How To Remove A Character In One Column
how would i go about removing just the first character in a column?

i.e: if i wanted to remove the 'A' in the ItemSKU column

ItemID ItemSKU ItemName
1 A09807969 Red T-Shirt
2 A09807970 Green T-Shirt
3 A09807975 Blue Skirt
4 A09807935 Grey Skivy
5 A09804567 Red Cap
6 A09803866 Blue Y-Fronts

Remove Large Datafiles?
I was trying to insert 100000000 rows when I ran out of disk space. I was forced to delete the table files by hand as I had no other way to free disk space. After doing this I created the table and inserted 100000 rows - all seems to run fine. I do have some very large data files under the var directory:

/data/mysql/var/

-rw-rw---- 1 mysql mysql 1073742047 Oct 5 10:52 luthien-bin.000033
-rw-rw---- 1 mysql mysql 1073742121 Oct 5 11:30 luthien-bin.000034

How do I clean these up?