MYSQL :: How To Prevent INSERT On Duplicate Rows With 30 Fields

Check For Duplicate Fields?
I have a product table, alot of the information can be the same for 2 different products but the one thing that absolutely differs them is the style number. Theres alot of products on the site and its hard to keep track of all of them. if there is no duplicates then every single field for the style number should be different. So If there was a duplicate product the style column would be the same in 2 or places. So what I am asking is if there is a way I can pull a field only if there is more than one occurance of it? Kinda like a SELECT DISTINCT in reverse. That way I can find diplicate products and remove them.

Actually an even better thing would be if there was a way I could set the table to reject any inserts if the style umber column is the same as one that already exists....but I dont know if thats possible either.

Stopping Duplicate Fields
I am using a MySQL database and have email address as the Primary Key.
I also store a username of which there should not be duplicates. I am
trying to check for this by doing the following:

$query5 = "SELECT username FROM member WHERE username='$username';";
$result5 = mysql_query($query5) or die('Error during subscription
process, please refresh the page and try again');
$row5 = mysql_fetch_row($result5);
if($row5 == $username1){
$errorlist = errorlist('This username is already taken',
$errorlist);
$errorform = 1;
$usernamestyle = ' ; color:red';
}else{
$querya = "UPDATE member SET username='$username1' WHERE
member_id='$user';";
}

But this doesnt seem to work.

Finding Duplicate Fields
discern a duplicate field among records in a table. For
example, suppose I have a table such that:

CREATE TABLE `sometable` (
`id` int(11) NOT NULL auto_increment,
`sometext` varchar(80) NOT NULL default '',
PRIMARY KEY (`id`)
)

I want to find all records where `sometext` = `sometext` in another
record. That is, I want to find those records where there is more than one
instance of `sometext` without regard to what `sometext` is. I may have a
row where `sometext` = "abc" and another where it is `zyx` and I do not
want to put a value for `sometext` into the WHERE portion of my sql select
statement. How can I find duplicated fields where I dont know what the value
of the field being duplicated is a priori.

Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement:

insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1)

When I run the select statement alone, I get the result I want:

+---+---+---------------------+---+
| 3 | 1 | software_feature_id | 1 |
+---+---+---------------------+---+
| 3 | 1 | 0 | 1 |
| 3 | 1 | 1 | 1 |
| 3 | 1 | 2 | 1 |
+---+---+---------------------+---+

But when I run the complete statement I get:

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 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1

This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.

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.

Insert A Duplicate Of An Existing Row
I have a table with an auto-increment columns (absid) and I want to take a row with a certain absid and re-insert it as a new row with a new absid (but all other columns as from the copied row). Running mysql 3.23.55. Might something like the following work?

insert into mytable (absid,*) values 0 select * from mytable where absid=20;

or something similar? With 3.23.55 I suspect it won't work anyway but I have no access to a version 4 installation and wouldn't want to upgrade if what I want to do is inherently not possible.

Replace Or Insert Duplicate
Just wondering how to write less PHP code, if MySQL does the job for me...

I have this really simple table:

CREATE TABLE `customers` (
`id` bigint(20) NOT NULL auto_increment,
`name` char(50) NOT NULL default '',
`address` char(50) NOT NULL default '',
`phone` char(9) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

Well, the point is, when I want to insert a new customer I do the following (in PHP)

- search for a customer by his name:
SELECT id FROM customers WHERE name='$name'
- if I get rows, the customer exists, so I update his data:
UPDATE customers SET name='$name',address='$address',phone='$phone' WHERE id='(the id)'
- if not, the customer doesn't exists, so I insert him:
INSERT INTO customers(name,address,phone) VALUES('$name','$address','$phone')

So... is there a way to do all of this with a simple MySQL statement like REPLACE or INSERT ON DUPLICATE KEY UPDATE ???

Insert On Duplicate Key Update
Is it possible so do something like this

"insert * from TempTabel on dublicate key update 'all fields'; "

So I don't have to write every column name. I need this, so I can use the same code on multible tabels. I could use replace, but then the foreign key in other tables are not updated.

How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?

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?

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

How To Handle Duplicate Keys In Insert
I have the following table definition:

CREATE TABLE `suggested_synonyms` (
`Last_Name` varchar(255) NOT NULL,
`Synonym` varchar(255) NOT NULL,
PRIMARY KEY (`Last_Name`,`Synonym`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The table stores mapping from a last name to a synonym.
I need to insert multiple rows into the table. Something like:

insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath');
the problem is that some of the mappings may already exist in the table.
I tried to use the ON DUPLICATE KEY UPDATE syntax to prevent duplicate entries, but apparently I am not doing it correctly:

insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath') ON DUPLICATE KEY UPDATE;
As I need to specify the columns to update.

Is there a way to insert multiple rows in a single query and still be safe from duplicate rows?

Insert/Ignore/Duplicate Entry
PHP

$sql="INSERT INTO USER SET
LOGIN = '".USER_LOGIN."',
PASSWORD = '".USER_PASS."'";

$result = db->query($sql);

if ($result->isError() ) {
return false;
} else {
$retrieveID = mysql_insert_id();
}

If I leave this query as is and I already have a LOGIN NAME that is a duplicate, I get a messy error message something like this...
Notice: Query failed: Duplicate entry 'jon' or key 2 SQL: INSERT INTO ...blah blah...

MESSY!!!

So I modified the query to:

PHP

$sql="INSERT IGNORE INTO USER SET
LOGIN = '".USER_LOGIN."',
PASSWORD = '".USER_PASS."'";

$result = db->query($sql);

if ($result->isError() ) {
return false;
} else {
$retrieveID = mysql_insert_id();
}

If there is a duplicate, I dont get the messy NOTICE error, which is fine. But if I already have that entry in my USER table, it will precede to get my next available MYSQL_INSERT_ID.

I dont want that to happen if it finds a duplicate using the IGNORE. I just want it to get that duplicate records ID instead, but if it doesnt find a duplicate record, get the last INSERTED record using the MYSQL_INSERT_ID()

Insert On Duplicate Key Update Question?
I have UNIQUE comp keys with 3 columns. I want to insert new rows if the data doesn't match one of the 3 columns. If they match, don't do anything. I am trying to do it with one query without doing select first and bunch of comparsion then either insert or update depending on the comparsion.

I am think of using this:
INSERT INTO mytable ('col1', 'col2', 'col3') VALUES ('mydata1', 'mydata2', 'mydata3') ON DUPLICATE KEY UPDATE col1=col1, col2=col2, col3=col3

is it going to work? I think it first tries to insert my data as a new rows if it is not duplicated. If duplicated, it will update the data with its old data. But update will ignore the operation since it's the same data.

V4 Error On Insert With Duplicate Entries
It appears to me that MySQL version 4 returns an error messge when doing an
Insert that results in duplicate entries. Version 3 did NOT return an
error - it dropped the duplicate entries and ran to completion. Version 4
seems to STOP when it encounters a duplicate entry, so that the records
before the duplicate are inserted and the records after the duplicate are
not inserted.

3.22.27.1 - previous ver MySQL that did not return error
4.0.16.0 - current ver MySQL that returns error.
Running on Red Hat Linux 7.3

Is there a way to change this behavior to the way it was handled in version
3? Are there configuration settings on MySQL that control 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.

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.

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?

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:

Insert 4 Fields
i have two tables: solutions and passwds
table passwds right now only has two fields: username and id
how can i insert the last 4 fields from table solutions right after the id field, on table passwds?

Combining Fields In One Insert
Question: I have an autonumber column followed by a varchar field. This varchar is a 2 character alpha to be followed by the autonumber id. Is there a way I can set this as default when creating the table?

INSERT SQL For Common Fields
Does anyone have any INSERT statements SQL Code for common address fields -
specifically looking for:

Country
State (US)
Provice (CAN)

Combine Fields From Many Rows
I have a table (see below) and I want to combine all the like named items with thier respective fruit and output that to a new table (1 name many fruit). I seem to be having problems getting the syntax down:

---------------------------------------------------------------------
ID | Name | Fruit
---------------------------------------------------------------------
1 | A | Mango
2 | B | Apple
3 | A | Pineapple
4 | B | Banana
5 | C | Pear
--------------------------------------------------------------------

I created another table called "myFruit" that have 2 field : Name, TheFruitILike

I want to update this table with the info from table "Fruit" . It should look like this:
-----------------------------------------------------------------
Name | TheFruitILike
------------------------------------------------------------------
A | Mango, Pinapple
B | Apple, Banana
C | Pear
----------------------------------------------------------------

What kind of command should I use?

MySQL INSERT From Form Fields
I'm trying to capture data from a members registration form and insert this into a MySQL database using PHP but keep getting errors although the code seems to read OK.

I can echo values from the database and get no connection errors so it has to be the data insertion portion of the code that's incorrect. Code:

Insert Using Foreign Keys And Other Fields
I am trying to create SQL commands to initialize a database with several tables tied together using foreign keys.

Table 1:
id1 int primary key auto_increment
state varchar

Table 2:
id2 int primary key auto_increment
fk1 points to table 1, id1
city varchar

So I can create two SQL commands:
INSERT INTO Table1 (state) VALUES ('Illinios'), ('Virginia'), ("Massachusetts');

INSERT INTO Table2 (city, fk1) VALUES
('Springfield', 1),
('Springfield', 2),
('Richmond', 2),
('Springfield', 3);

My problem is that if I create a new dataset using the same two commands, but add a state at the beginning of the file (or remove one), the keys change, and the 2nd insert statement will associate the cities with the wrong state, or fail.

I would like the second statement to be something like:

INSERT INTO Table2 (city, fk1) VALUES
('Springfield', SELECT id1 FROM Table1 WHERE state='Illinois');

or something like that. Is there any way for mySql to do this?

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Swap Two Fields Bertween Two Rows
I have a table in which a field (ordine) is used for the orderer output of data.

This is not an index, since data are subdivided in different categories and therefore the value is not unique for the whole table.

Sometimes I need to change the display order and I was used to use a query like this one:

UPDATE globalmoneta as mon1,globalmoneta as mon2 SET mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";

probably not very efficient, but acceptable since this operation is not often performed.

Unfortunately, after a server upgrade this query is no more working. I suspect this is due to a different mysql version but I can not check it.

Any idea on how to obtain the same task with a different query?

Select Only Rows Of 2 Fields That Does Not Repeat
I would like select only rows where registerNo AND entryid that does not repeat.
Example, from the table only one row of 'registerNo=1 and entryid=sqbiiphiu1' and the last 5 rows will be selected.

I'm using php to build this. i'm not sure if distinct can help. this is wat i thought of
$query = "SELECT DISTINCT * FROM fn_vote WHERE registerNo = '".$registerNo."' AND entryid='".$entryid."'";

Show Rows With No Matching Fields
here is my query which probably needs some more advanced join statements:

select * from orders o, customers c, orders_styles os, styles s where o.customers_id = c.customers_id and os.orders_id = o.orders_id and os.styles_id = s.styles_id group by o.orders_id

the problem is that sometimes, because of user input, there will be no matching fields in the tables orders_styles or styles (no rows with matching orders_id exists in table orders_styles to table orders). I still want to show these rows however. Right now, as you can see it only displays rows that have matching rows in orders_styles and styles.

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value.
example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates).

how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?

Insert Rows
I have deleted a bunch of forum posts to test something. Now, i want to enter the data rows back into the table without deleting new content that was added after my changes.
I tried REPLACE, but it didn't add the old rows back in.
What syntax shoudl i use. I'm running these queries by using mysql in terminal.

Insert 300+ Rows
I need to insert roughly 300 rows of data from a file that is being exported from another system. I can get it in the following format, but can change accordingly.

So the text file will look something like the following. I already have the table setup for each fields and it's just a matter of taking the text file and auto-inserting the rows into the database. Can it be done

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?