on duplicate key code example
Example 1: mysql on duplicate key update
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;
Example 2: sql insert or update
-- Oracle: Example for Insert or update in t1 from t2 values
MERGE INTO table1 t1
USING table2 t2
ON (t1.CODE = t2.ID)
WHEN MATCHED THEN
UPDATE SET t1.COL1 = t2.VALUE1
WHEN NOT MATCHED THEN
INSERT (CODE, COL1) VALUES (t2.ID, t2.VALUE1);
-- MySql (makes a INSERT + DELETE if existing)
REPLACE INTO table1 (pk_id, col1) VALUES (5, 'aaaa');
--
INSERT INTO table1 VALUES (key, generation)
ON DUPLICATE KEY UPDATE (key = key, generation = generation + 1);
Example 3: mysql on duplicate key update
UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
Example 4: duplicate entry for key
The error says it all:
Duplicate entry ''
So run the following query:
SELECT unique_id,COUNT(unique_id)
FROM yourtblname
GROUP BY unique_id
HAVING COUNT(unique_id) >1
This query will also show you the problem
SELECT *
FROM yourtblname
WHERE unique_id=''
This will show you where there are values that have duplicates. You are trying to create a unique index on a field with duplicates. You will need to resolve the duplicate data first then add the index