MYSQL :: Update INT UNIQUE Column - Duplicate Entry Error

Duplicate Entry For Unique Field
i have a table with two fields (id and myfield). id is a primary key not nll
auto_increment, and myfield is set as unique...

Due to unique setting for myfield, if i try to insert the duplicate entry
for myfield, i will get error message. i was wondering if this is a way that
I can avoid error message and let mysql just ignore my duplicate entry, and
i can carry on.

Duplicate Entry Error ?
I have this table, now i won't it to be possible to have duplicated entrys. Is that possible with this table, or would i have to give it a column with autoincreasment or something?

Duplicate Entry Error?
I am trying to insert new data into my database via a form, but i get this error:

Duplicate entry Ɖ' for key 2

I think I have made a mistake while made my MySQL database.

ERROR: Duplicate Entry '1' For Key 1
I'm working on this little project. I'm learning the basics right now of SQL. I'm currently getting this error

Duplicate entry '1' for key 1

I have no idea what it means. I'm working with two files here. One is creating tables, while the other is inserting data. If you have any idea what the problem is I'd appreciate it. Here's the code.

creatmovie.php

<?php
//connect to mysql
$connect = mysql_connect("localhost", "root", "pass")
or die("Check your connection");

//create the main database
$create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite")
or die(mysql_error());

//make sure our recently created database is the active one
mysql_select_db("moviesite");

/....................................................................

Duplicate Entry '0' For Key 1 Error
I have a table which is like:

[MYSQL]
CREATE TABLE word (
wordno DECIMAL(6) NOT NULL,
lemma VARCHAR(70) NOT NULL
);
ALTER TABLE word ADD CONSTRAINT pk_word PRIMARY KEY(wordno);
[/MYSQL]

and I have a word.sql data file like below:

[MYSQL]
(0, 'entity')
(1, 'physical thing')
(2, 'thing')
--and goes on
[/MYSQL]

I am trying to fill the db with data and use the command:

LOAD DATA INFILE 'word.sql' INTO TABLE word;

but I am getting the error:

#1062 - Duplicate entry '0' for key 1

Why I am getting this error? Is this because of PHP or MySQL version? I am running MySQL ver 3.23.49 with PHP 4.3.6.

SQL/DB Error -- [Duplicate Entry 'Improve' For Key 2]
am getting the error below. Any idea as to the cause,

SQL/DB Error -- [Duplicate entry 'Improve' for key 2]

ERROR 1062: Duplicate Entry For Key 2
I get that error when I try to update a table in my database

This is my table
+----+------------+---------+-----------+
| ID | CPRNR | NAME | SURNAME |
+----+------------+---------+-----------+
| 1 | 1234567890 | Ole | Hansson |
| 2 | 9876543211 | Ulla | Jensen |
| 3 | 1234321234 | Ole | Olsson |
| 4 | 7654321234 | Petter | ?es |
| 5 | 9876543213 | Ulla | Jensen |
+----+------------+---------+-----------+
5 rows in set (0.00 sec)

And my update lools like this:

update patient set cprnr='9876543211',fornavn='Ulla',efternavn='Jense n'
where id='5';

This is the error then :

ERROR 1062: Duplicate entry '9876543211' for key 2

I don't get it.

Duplicate Entry Error W/ Accented Character
In the process of moving my database over to a new server I'm running into a duplicate entry problem, related to an extended character (acute accented lowercase e: Ã© )

CREATE TABLE `product_additional_keywords` (
`prod_id` int(11) NOT NULL default Ɔ',
`keyword` varchar(90) NOT NULL default '',
PRIMARY KEY (`prod_id`,`keyword`)
) TYPE=MyISAM;
And here is the relevant insert and error message:

INSERT INTO `product_additional_keywords`
( `prod_id` , `keyword` )
VALUES
( 305, 'mÃ©thode' ) , ( 305, 'methode' ) ;

MySQL said:
#1062 - Duplicate entry 馉-methode' for key 1
MySQL version is 5.0.27. I am using phpMyAdmin 2.8.2 to access the remote database (no direct access). I am uploading an SQL file containing the insert, since it has about 8k records to insert.

The file is residing locally on my Mac (OS X) and in phpMyAdmin it is expecting character format of utf-8. I am not entirely certain that the encoding on my Mac is utf-8 (TextWrangler reports it is "UNIX" format, with Mac OS Roman encoding; however, the accented e shows up as garbage in TextWrangler but shows correctly in the phpMyAdmin error message)

The collation of the table is latin1_swedish_ci (like all my other tables). Which seems a little weird to me -- is that the proper setting for normal use?

I have a lot of accented/extended characters in my data and am really freaked out about the idea of not being able to properly copy the data over to the new server. I know next to nothing about encodings and how to properly approach storage, backup, and replication.

Error Handling When Adding Duplicate Entry
I have an application with MS Access 2000 as the front end and MySQL as the
back end. All was well until I upgraded the MySQL (Linux) server.

The Problem: I insert data into a cumulative table. Before when I did
this, and there were duplicate entries, the duplicate entries were rejected
and I got a return code with the number of affected rows (number added).
Now, I get a MyODBC error and the application stops when trying to add
duplicate entries.

3.22.27.1 - previous ver MySQL that did not return error
4.0.16.0 - current ver MySQL that returns error.

The MyODBC was not updated on the client (Windows) computer. However, I
tried updating MyODBC from ver 2.50.39 to ver 3.51.03 with no change in
results. So I am guessing that MySQL has changed what it passes to MyODBC
when it encounters this condition.

How can I set MySQL (or MyODBC or whatever) so that the insert query does
not halt the insert when it encounters a duplicate entry?

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?

ERROR 1062 (23000) At Line 6427: Duplicate Entry ...
I have been performing mysqldump many times over a year on this database to resync a replication server from time to time. This morning to my shock and horror my attempt to load the sql file into my backup database produced this result:

Duplicate Column Name Error
I am trying to setup a database to use for a Faultreporting system. i found one online and it gives me an error each time i try to set it up. Its a MYSQl error "Duplicate column name 'id'" here is the table structure for the tabel in question. Code:

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

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

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

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

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

DELETE DUPLICATE Except LAST ENTRY?
- HOW TO DELETE DUPLICATE FROM MYSQL EXCEPT LAST ENTRY?

CREATE TABLE `chatone` (
`id` int(255) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`message` varchar(250) NOT NULL default '',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `chatone`
--

INSERT INTO `chatone` VALUES (1, 'test', 'orange', &#55614;&#57159;-03-13 15:25:24');
INSERT INTO `chatone` VALUES (2, 'test', 'orange', &#55614;&#57159;-03-13 15:25:24');
INSERT INTO `chatone` VALUES (3, 'test', 'apple', &#55614;&#57159;-03-13 15:25:48');
INSERT INTO `chatone` VALUES (4, 'donald', 'hello', &#55614;&#57159;-03-13 15:25:48');

Duplicate Entry In An Auto_increment Key
I have a table with an int(11) primary key that is set to auto_increment. Now I have this script that keeps on failing because it says that there is a duplicate key but it doesn't even exist.

It's now at 1149447582 and it's stuck there. Is this a bug or something? I'm using MySQL 4.1.12 on CentOS 4.3 (64-bit).

Unique Entry In A Table
I am having a problem with trying to work out how I could select the lowest unique number in a field called MONEY. I am trying to find the record with the least money, but no other record in that table can have the same amount of money.

Here is the table:

CREATE TABLE lubidEntries (
partnerid smallint(5) unsigned NOT NULL default '0',
lubidCamp smallint(5) unsigned NOT NULL default '0',
keyword varchar(40) NOT NULL default '',
MONEY varchar(40) NOT NULL default '',
status enum('current','old', 'lubid') default NULL,
time datetime NOT NULL default '0000-00-00 00:00:00',
numberid int(10) unsigned NOT NULL default '0',
KEY lubidentries (partnerid,status,numberid)
) TYPE=MyISAM;

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()

MySQL Question: Duplicate Entry '*****' For Key
I'm trying to make a backup copy of some table, but I keep getting the following error:

#1062 - Duplicate Entry '127' For Key 1
Table set up like this:

`id` `makeid` `name` (the id is primary auto-increment)

This is for a list of car makes and models, I also have a make table that the makeid links to.

I currently have 127 rows in this table. I try to add another with this SQL:

INSERT INTO `ct_a_model` ( `id` , `makeid` , `name` )
VALUES (
'', 15, 'FEROZA II'
)

I thought that if I don't specify an ID then it would auto increment, as it has done with all the other 127. Also if I specify an id:

INSERT INTO `ct_a_model` ( `id` , `makeid` , `name` )
VALUES (
128, 15, 'FEROZA II'
)

I still get the same error. Can anyone point me in a direction?

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.

Duplicate Entry On Varchar Field PK?
I test this script:

drop table if exists TMP_TEST;
create table TMP_TEST
(
col1 VARCHAR(10) ,
primary key (col1)
);
insert into TMP_TEST values('test');
insert into TMP_TEST values('TEST');

The 2th insert sql will say "Duplicate Entry 'TEST' for key 1",But the 'test' is NOT equale to 'TEST' string! How to resolve this problem?

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?

'Duplicate Entry', But Would Think ISNULL(Key) Prevents That
MySQL 5.0.27, PHP 5.

I am inserting records into a table that already contains records. I don't want duplicates, so there's a unique index. Good query design should let me eliminate potential duplicates; but, I am getting the error "Duplicate entry ... for Key 1."

INSERT INTO filter_cache
( fid_part, pn_oem, descr_oem, )
SELECT DISTINCT
p.pid_part, p.pn_oem, p.descr
FROM (part p LEFT JOIN filter_cache fc
ON p.pid_part = fc.fid_part)
INNER JOIN supplier_part_join sp
ON p.pid_part = sp.fid_part
WHERE ISNULL(fc.fid_part)
AND sp.pn_supplier LIKE '$search_str'

Duplicate Entry 'user@email.com' For Key 2
I have a form which is returning this generic error when encountering a duplicate entry in the database. I would like to change this to a custom value. How would this be achieved?

Strange Results When Trying To Do A Duplicate Entry Check
My goal with this is to check a database for duplicates using the first 25 characters of the "title" column in a database called "jos_content." The query should find the entries whose titles appear more than once and then the second part should take those entries and mark them for trash (state=-2 is trash).

CREATE TEMPORARY TABLE dupes
SELECT Left(title,25)
FROM jos_content
GROUP BY title
HAVING COUNT(*)>1; # Affected rows: 4

UPDATE jos_content
SET state="-2"
WHERE (title) IN (SELECT Left(title,25) FROM dupes); # MySQL returned an empty result set (i.e. zero rows).

The result for the first part finds entries but I suspect they aren't duplicates becuase the second part returns zero entries. The actual database has about 16 duplicate rows based on the first 25 characters of the title column.

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:

Error #1293: There Can Be Only One TIMESTAMP Column With CURRENT_TIMESTAMP In DEFAULT Or ON UPDATE Clause
I am using MySQL 4.0/4.1 version. And I am trying to add two timestamp columns to a single table. The columns are insert_date and updat_date to capture the date/time the record was initially inserted as well as the date/time the record was last updated respectively.

When I try to set one column (insert_date) with a default value of CURRENT_TIMESTAMP and the other column (updat_date) with ON UPDATE CURRENT_TIMESTAMP, I end up getting the following error:

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Is there any way around this problem?

Update Entry
Below is the code that I am using. It does the updating for me which is perfect but I cannot get the date part right. It updates all entries. I want it to update any entry that is one day older than presentday.
so update entry if curdat = yesterday.. Please help!

result = mysql_query("UPDATE `shows` SET status=81 WHERE date <= (CURDATE() - INTERVAL 1 DAY)");

Multiple UPDATE Entry
I just want to update 2 or more things at the same time... but dunno how... here's what i would use:Code:

UPDATE users SET location = 'changed' , password = 'new pass' WHERE username = 'david'
OR SET location = 'changed too' , password = 'new pass too' WHERE username = 'brett'

But that doesn't work, (it's not like i thought it would anyway)... how would i update 2 or more different fields at the same time?

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?

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.

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

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

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

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

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

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

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

Unique Row Not Column?
I know Mysql is able to prevent duplicates being entered in the one column, via the 'unique' feature.

Is it possible to stop rows of the same values being created.

For example
joining two or more columns in a row, and merging the values into a singular value, upon which the 'unique' feature may work. Or is their more of a simple way of doing this.???

Invoice_ID Products UNIQUE
56 ---------- X --------- 56X
56 ---------- Y --------- 56Y
56 ---------- X --------- 56X
57 ---------- Y --------- 57Y

Unique Column
Each time I try to insert multiple rows of data, MYSQL will stop when it finds the first duplicate. I'd like to know how I can have it continue on and either suppress the error message completely, or put the duplicated entries into an array and output them afterwards.

Unique Column
I googled but cant find it: I need to know how to create a column thats unique when I make my table.

Duplicate A Column
I have to copy an excisting column (with data) from my table into the same table. The idea is to copy all the excisting data from that column into a new column.
How to do?

Update If Duplicate Found
Im currently running a statement that inserts a new row into the table if a duplicate row is not found:

PHP

$db->Execute("INSERT INTO tee_time ( SELECT $locationid AS locationid, (SELECT courseid FROM course_conversion WHERE coursename = '$currcourse' LIMIT 1) AS courseid, '$currcourse' AS coursename, '$currtime' AS teetime, '$currdate' AS teedate, '$currcost' AS cost, NOW() AS grabdate FROM tee_time WHERE coursename = '$currcourse' AND teetime = '$currtime' AND teedate = '$currdate' HAVING COUNT(*) = 0)");

The problem is if a row is found I need it to update that row instead of just skipping it. On duplicate wouldnt work because teetime and teedate can be the same in multiple rows.

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.

ON DUPLICATE KEY UPDATE Syntax
I have two tables with identical columns. I want to update records in tbl1 with record information from tbl2.Code:

INSERT INTO tbl1 SELECT * FROM tbl2 ON DUPLICATE KEY UPDATE SET tbl1.col=tbl2.col, tbl1.col2=tbl2.col2

I seem to be missing something 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.

Should A UNIQUE Column Have An Index?
Would I improve performance if I was to give each column that is expected to
have a unique value, a key/index?

Can I Have Both 'Unique' & 'Index' On A Column?
I have a mysql table and I use wizmysqladmin to make modifications to it.

I have declared 'Index' on a column while setting up the table.

later, I understood that elements in that column are 'unique'.
So, I have declared 'unique' on that column.

Now, that column is having both 'index' and 'unique'

is it ok or shall I remove one of the two?