MYSQL :: Remove Duplicate Rows

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.

How To Remove Empty Rows From My Mysql
i recently uploaded data from text file .Bu unfortuently there were empty lines between each records in the text file and now after transfering the data to mysql i got one empty row for those empty lines. Could any oone tell me how i can remove those empty rows.

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.

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

Rejecting Duplicate Rows
How can I make MySQL reject duplicate rows when inserting?

The table is quite large: 100+ columns and millions of rows.

MySQL doesn't allow me to create a UNIQUE INDEX for all the columns
(16 columns is the max.)

The brute force method (selecting the new candidate row and inserting
only when not found) is way too slow.

Eliminating Duplicate Rows
Is there a way i could query the database to find all duplicated rows based on the column `name`?

Listing Duplicate Rows
I have this table with a large list of "codes". I would like to know how to setup a SELECT query to list all duplicate code entries from a table. Here is an example of what I am referring to:

-Before-
1000, 1001, 1002, 1002, 1003, 1004, 1004, 1004, 1005

-After-
1002, 1002, 1004, 1004, 1004

I hope this makes sense now. Now, I have attempted trying to figure this out myself but I seem to have an issue with trying to find a solution using MySQL 4.0.22. I would really appreciate it if someone could assist me with this. There maybe other people who could benefit from this kind of solution also.

Deleting Duplicate Rows
Ive created a db for contest entries, and it seems that many people have entered multiple times. What I want is to create a table which has only unique address'.

What would be the best way to create such a table?

Deleting Duplicate Rows
I currently have a table with the fields; id, author, downloadurl, name
however I have some duplicate rows around 200....I want to delete the duplicate rows, downloadurl contains unique values so I can identify the duplicates from that.

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.

Deleting Duplicate Rows
I'm having difficulties at the moment with a suburbs table. The table is as follows:

suburbs(id, name, state)

id is the PK and (name, state) should be a unique key. The problem is that we didn't realise the need for the unique key until now. What is easiest way to delete all records from the database which violate this contraint?

In other words, if I had 2 records with name='Wallan' state='VIC', what is the easiest way to only keep 1?

Rejecting Duplicate Rows
How can I make MySQL reject duplicate rows when inserting?

The table is quite large: 100+ rows and millions of rows.

MySQL doesn't allow me to create a UNIQUE INDEX for all the rows (16
rows is the max.)

The brute force method (selecting the new row and inserting only when
not found) is way too slow.

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.

Duplicate Rows In UNION
I have 2 joined query:

(select * from TABLE1 where data like '%A%' and data like '%B%')
UNION
(select * from TABLE1 where data like '%A%' OR data like '%B%')

I want to show result of the first half of query come before the result of the second one. How could I achieve this?

Select Duplicate Rows
In a table, i have records about phone number and stateID, sometimes there are rows that have the same phone number with different stateID (assume stateID has value from 1 to 50, or 100), I want to find out duplicate phone numbers with stateID value of 100 (those same phone numbers also contain stateID of value between 1 and 50). Can someone please tell me what would be the query for selecting this?

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?

Upload Cvs And Ignore Duplicate Rows
I uploading a cvs file using phpmyadmin, it's of web users for my website... i only want to added rows that are not already there..

The rows that are already there have been slightly alterered i.e they they have a password in them..

Will phpmyadmin Ignore duplicate rows function still add the row of a user as it is slight different.. or is there another way to add rows that will only use a specific field to determine weather it is a duplicate or not

MySql UPDATE With Duplicate Rows
I'm trying to run an update on a linking table, the update is running into a Primary Key
constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: Code:

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.

How Can I Use The DISTINCT (or Another Argument) To Choose Duplicate Rows In My DB?
I have a form where users submit their information. Unfortunately, some people tend to submit the form twice, sometimes 3 times. Is there a way I can select duplicate rows in my table?

How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?

Preventing Duplicate Rows On Form Entry
I have written an contact database for my company intranet. I want to prevent users from submitting new contacts into the database if the contact already exists. I have written an unsuccessful php script that tries to query the database prior to inserting the fields from the entry form. ("if first and last name columns match, do not insert").

This seems like a common database practice, but I can't find out how to do this.
My code below:

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

Join Rows Into One Result
I'm trying to select letters in a word from a table with the ascii representations. I am selecting the letters successfully, in the correct order. The rows returned give me a letter in each row. I would like it to return one row with the joined word.

aka. It is returning
a
r
d
v
a
r
k

I would like
ardvark

This probably has to do with GROUP BY, but I don't know any functions to join characters into a string.

How Can I Use A Join With Possibly Nonexistent Rows?
here's the situation: I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

where "user id" and "quote id 2" come from the ratings table and will be NULL if the quote hasn't been rated by the current user, and will be equal to the current user's id and the quote id if it has already been rated.

So far, I think the closest I've come to success was was using this subquery (which would probably be terribly slow even if it did work):

SELECT quotes.*, user.username, ratings.quoteid AS votedid, ratings.userid AS votedby
FROM quoteratings AS ratings
INNER JOIN quotes AS quotes
LEFT JOIN user AS user ON user.userid = quotes.userid
WHERE quotes.quoteid IN (SELECT quoteid FROM quotes WHERE average >= 0 AND approved = 1)

I hope that explanation makes sense, I know there has to be a fairly easy way to do this, but I just can't find it.

Join To Find Rows Not In Second Table
I'm trying to write a SELECT for MySQL 4.0 using a JOIN. I can get it to work in v4.1 using a subquery, but my ISP provides v4.0 only.

I've got 2 tables:
- group: Describes groups that exist, key is group_id
- usergroup: Members of groups - has user_id and group_id

I want to find which groups a user **doesn't** belong to (say user_id=3).

In MySQL 4.1+ I can do this using a subquery:

SELECT group_id FROM group
WHERE group_id NOT IN (
SELECT group_id
FROM group g,usergroup ug
WHERE ug.group_id = g.group_id
AND ug.user_id = 3
)

This query doesn't work in MySQL 4.0, no I need to use JOIN (I think). I've been searching forums and trying things out, but I cannot figure out how to make it work.

Listing Multiple Rows In One Row With JOIN
I have assets and tasks in my table. There are multiple assets assigned to each task. What I want to do is to list the asset followed by all the tasks linked to it in one row.

SELECT * FROM asset
LEFT JOIN task ON (asset.id = task.asset_id)
WHERE asset.id=task.asset_id
GROUP BY asset.name

The above is close, but it will only join the first matching entry from the task table, the result I was hoping to get was something like.

asset1.id asset1.name task1.name task1.status task2.name task2.status

Is there an easy way to do this in SQL?

Getting Number Of Rows From Left Join Table
PHP

SELECT bulletin.id AS bid, bulletin.bulletinname AS bname, bulletin.bulletindesc AS bdesc, bulletin_post.id AS postid, DATE_FORMAT(bulletin_post.postingtime,'%M %d, %Y %l:%i%p') AS postdate, bulletin_post.bulletintitle AS ptitle, member.screenname AS mname, member.id AS posterid FROM bulletin LEFT OUTER JOIN bulletin_post ON bulletin.id = bulletin_post.bulletinid AND bulletin_post.postingtime = (SELECT MAX(postingtime) FROM bulletin_post WHERE bulletinid = bid) LEFT JOIN member ON member.id = bulletin_post.memberid WHERE bulletin.active = 1 ORDER BY bulletin.bulletinname ASC

My question is how would I get the count of rows if any from the bulletin_post table?

Creating Non-existent Rows In Query With Join
I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query:

Can't Use JOIN And SUM To Roll Up Rows Into Multiple Columns
Let's say I've got the following table "Eats" that contains row after
row of Calories consumed by children eating lunch at the school
cafeteria. The "ID" column is an autoincrement field I added, but it
doesn't seem to be doing me any good.

mysql> SELECT * FROM Eats;
+------+------+------+----------+----+
| dow | who | sex | Calories | ID |
+------+------+------+----------+----+
| Mon | John | Boy | 2600 | 1 |
| Mon | Tom | Boy | 1900 | 2 |
| Mon | Jane | Girl | 1200 | 3 |
| Tue | Tom | Boy | 1600 | 4 |
| Tue | Jane | Girl | 1300 | 5 |
+------+------+------+----------+----+

The output I WANT is a table with total calories broken down by gender
and day, i.e., one (and only one) table with a "Boys" total and a
"Girls" total. In this case I want to see:

+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 1200 |
| Tue | 1600 | 1300 |
+------+------+-------+
It's important to have One table with Two headers, not the other way around.

Not knowing any better I tried a JOIN and came closest with:
mysql> SELECT a.dow AS "Day",
-> SUM(a.Calories) AS "Boys",
-> SUM(b.Calories) AS "Girls"
-> FROM Eats AS a
-> JOIN Eats AS b
-> ON (a.sex="Boy" AND b.sex="Girl" AND a.dow=b.dow) GROUP BY a.dow;

Which produces:
+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 2400 |
| Tue | 1600 | 1300 |
+------+------+-------+

Everything is right except Mon-Girls, which is twice the real value.
I figure this is due to the pairing of Jane-John and Jane-Tom, causing
Jane's meal to get counted twice. But that pairing is necessary to make
sure we count all the boys' Calories too.

Am I overlooking something obvious? Are JOINs and SUMs just a bad idea?
Most importantly, is there some other way to accomplish the task? I've tried
many, many variations on the above code (with and without using the ID field)
and gotten nowhere.

For this simple example I could do SELECTs INTO variables, but that solution
doesn't generalize to hundreds of rows, nor cases where the children are
further broken down by age. Code:

LEFT JOIN Produces Extra Rows
The `suggestions` table contains 2,265 rows. The `ALL_PHS_SCHEDULES` table contains over 22,000 rows. I have done my homework and I read that the Left Join return ALL rows from the first table and the matching ones from the other table. I am expecting exactly 2,265 but the query is returning 2,734 rows.

Quote: SELECT *
FROM `suggestions`
LEFT JOIN `ALL_PHS_SCHEDULES` ON `suggestions`.course_number = `ALL_PHS_SCHEDULES`.course_number
AND `suggestions`.section = `ALL_PHS_SCHEDULES`.section AND `suggestions`.id = `ALL_PHS_SCHEDULES`.id
ORDER BY `ALL_PHS_SCHEDULES`.`course_number` , `ALL_PHS_SCHEDULES`.section ASC

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

MySQL4.1 And PHP
I had to reformat my drive, so I got and installed Apache2, and installed PHP 4.3.11 and conf. both and all is fine...
Then I install MySQL 4.1 and MySQL Administrator.

Then PHP pages are PAGE NOT FOUND, and I can not add users in the MySQL Administrator, is keeps saying can not update user information....
Or does someone have a older version of MySQL 4.1....
The last one I had that was MySQL 4.1 but NEVER gave me a problem.... Until I dumped the old copy thinking I would get a freash copy.

Mysql4
Why does the cpu usage of mysqld stay at 98-99% when there is like no process running in showprocess
very little traffic as well
it used to fine; taking only 3% max cpu, but now it stays at near 100% constantly,
why does this happen?
I asked around many forums, but no one ever knows the answer to this similiar type of problems
no process; light traffic; extreme high cpu usage
dedicated dual xeon server with 1G Ram on freeBSD

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

Adding Missing Rows In Table In 1 Select (+ Join) Command.
Hi all! Here's what I need to do :

I have two tables :
A B
a b c d
---- ----
1 z 1 k
2 x 5 l
3 c 6 j

I need a SELECT with JOIN that would give me :
A
a b
----
1 z
2 x
3 c
5 NULL
6 NULL

so I need to add the missing rows from the A.a and B.c JOIN,
how can I do that ?

I can't use a Union 'cause I can't use MySQL version 4.

Installing Mysql4.0.23
I need start mysql4.0.23 in Linux RedHat7.2, I was install mysql4.0.23 server successfull, but it dont start. I need configure it for my university project.

Can I Use Mysql4.1 On Aix 5.3 Environment?
I already know mysql4.1 binary does not provide for aix5.3.
So, can I use MySQL4.1 on AIX5.3 environment if I build from sourse file?

Character_set_connection + Mysql4.1 + My.cnf
I 'm trying to set character_set_connection variable in my.cnf with no
luck. Does anyone know how to do it?

Raid And MySQL4.1
I had a forum and all was fine I wanted to install a raid card, so I backed up the forum files and did a database backup with MySQL Administrator...
Installed the card and got Windows XP up and running, reinstalled everything, and restores the forums and database backup.
But now the forums are all messed up..... When you load the forum, it only loads half the page, then you refreash and it will load more sometimes less, but it is never the same.....
Somethmes it will put the overall_header.tpl at the top and half if it at the botton.... It is werd....
Is there a problem with switching from a non-raid system over to a raid system with the MySQL and the way it handles the databases?

Back To MySQL4
I have built a website using PHP5 and MySQL5 only to find that I cannot find a host that uses MySQL 5 yet…
I have now changed the site to access the database via PDO.
Problem is, PDO does not currently work with MySQL5 (correct? If anyone could tell me how to get PDO to work with MySQL5 this would be brilliant!)
I therefore need to load MySQL4 onto a machine that is already running MySQL5.
Do I need to uninstall MySQL5 first?

Query Doesn't Work With Mysql4
Can someone tell me what's wrong with the mysql query below? It does work on mysql 5 but gives me an error with mysql 4.

SELECT rev_profile_indiv. *
FROM rev_profile_indiv, rev_profile_kids_indiv
WHERE 1
AND reviewing
IN ( 1, 0 )
AND active
IN ( 1 )
AND contract
IN ( 1 )
AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) >= UNIX_TIMESTAMP( &#55614;&#57156;-12-15' )
AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) <= UNIX_TIMESTAMP( &#55614;&#57158;-12-15' )
GROUP BY rev_profile_indiv.username
ORDER BY lastname
LIMIT 0 , 30;

MySQL4 -> MySQL5 Problem
This query works in MySQL4:

SELECT news.news_id, news.news_headline, news.news_content, news.news_date AS true_date, DATE_FORMAT(news_date, '%H:%i, %D %M %Y') AS news_date, news.news_author, news.source, news.sourceurl, news.headlineimage_id, users.forum_profile_url, news_images.headlineimage, news_images.width, news_images.height, news_images.alttext, news_images.companyurl, COUNT( news_comments.comments_id ) AS news_count FROM news JOIN users, news_images ON news.news_author = users.username AND news.headlineimage_id = news_images.headlineimage_id LEFT OUTER JOIN news_comments ON news.news_id = news_comments.news_id WHERE news_date >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY news.news_id ORDER BY true_date DESC;
When I run this same query on the same database on MySQL5 it will not run and bails with:

ERROR 1064 (42000): 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 news.news_author = users.username AND news.headlineimage_id = news_images.hea' at line 1

Mysql4.1 Install Question
------=_NextPart_000_0011_01C355D2.C79798C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I installed the 4.1 alpha release, which I probably shouldn't have, but =
I did because the 4.0 was giving me access denied error (before I found =
your mailing list). However my problem got complicated because I =
installed this on a server which would eventually be a web mail server. =
So I installed Postfix after this which is the order it should be, after =
mysql. Next, I tried to install courier-imap. While configuring, it gave =
an error that said 'mysqlclient cannot be found. I looked for it on the =
server and sure enough it was not there. Mysql can be accessed and is =
functional on its own but when linking to courier-IMAP, I cannot even =
install IMAP because one of my options is 'with_authmysql' (which I =
cannot find in the Mysql folder, either). I also followed the =
installation instruction of mysql religiously. SO can anyone give me an =
idea as to what went wrong and how it can be fixed. Or how I can =
downgrade to the production release 4.0 without breaking any established =
links currently. Any suggestions would be helpful.
------=_NextPart_000_0011_01C355D2.C79798C0--

Remove Duplicate Rows - MySQL4 JOIN