How to avoid duplicate primary key values with INSERT INTO SELECT and NOT EXISTS

insert into on duplicate key update
mysql insert or update if exists without primary key
insert on duplicate key update is equivalent to
mysql insert if not exists else update
sql conditional insert if not exists
mysql insert if not exists else do nothing
mysql on duplicate key update multiple values
how to insert duplicate rows in sql with primary key

I am trying to insert rows into an empty table that store client information from another table. The primary key is ID and I use the following query to select records:

INSERT INTO client (id, name, surname, surname2, dob, phone, email, address) 
SELECT DISTINCT NVL(cl_dni, floor(dbms_random.value(10000000,100000000))), 
cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address
FROM purchases WHERE NOT EXISTS(SELECT id from client WHERE client.id = purchases.cl_dni);

My main goal is to generate a random integer for id if it is null and make sure that I don't add duplicate id values to the table, however it gives me an unique constraint violation error. Does this mean there is problem with the WHERE NOT EXISTS clause? How can use INSERT INTO SELECT to avoid duplicate primary key values?

What is the unique constraint on client table? Can I assume that there are dups in purchases table as you are using distinct while fetching the data? There might be a possibility that there are multiple records in purchases one with a valid client id and one with null client id but other attribute values are same. This way you are trying to insert 2 records in client table from purchases one with a valid id and one with a random value, but same attributes for the unique constraint defined on client table.

Insert if NOT exists – duplicate key problem – Vedran Kesegic Blog, We want to avoid these errors, all of which are seen in these tests: Error 2601, severity level 14: Cannot insert duplicate key row in object '%. Id INT IDENTITY PRIMARY KEY CLUSTERED, SELECT 1 FROM dbo. As mentioned, we will do a loop that tries to insert a value if it does not already exists. Here is the query to avoid inserting duplicate rows in MySQL. We will set it with the insert command to insert records in the table − mysql> insert into avoidInsertingDuplicateRows(FirstValue,SecondValue) values(10,20); Query OK, 1 row affected (0.24 sec) mysql> insert into avoidInsertingDuplicateRows(FirstValue,SecondValue) values(10,20

Maybe the best way is to create a sequence, starting at 10000000:

 CREATE SEQUENCE SEQ_ID
     START WITH     10000000
     MAXVALUE 99999999
     INCREMENT BY   1
     NOCACHE
     NOCYCLE;

and make something like this:

INSERT INTO client (id, name, surname, surname2, dob, phone, email, address) 
SELECT NVL(cl_dni, SEQ_ID.nextval), 
cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address
FROM purchases WHERE NOT EXISTS(SELECT id from client WHERE client.id = purchases.cl_dni);

The problem of this approach is that you could generate with the sequence an existing cl_dni.

13.2.6.2 INSERT ON DUPLICATE KEY UPDATE Statement, INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE an INSERT statement increases the auto-increment value but UPDATE does not.) try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple a table having more than one unique or primary key is also marked as unsafe. insert into t1 (a,b,c) values (1,2,3) on duplicate key update c=c+1; update t1 set c=c+1 where a=1; (The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

Well random does not garantee unique, and the NOT Exists part will not find anything because the target table is empty.

Its not that easy in pure SQL but as you are using an Oracle DB you can easily do it in plsql. This should work in principle :) . Not tested.

declare
    iOffset pls_interger;
begin
--  Use max value as offset
    select max(cl_dni) into iOffset from purchases;
--  Use 1 if no id exists at all
    iOffset := nvl(iOffset,1);  
--  Loop thru all purchases
    for rec in (SELECT distinct cl_dni,cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address FROM purchases) loop
        INSERT INTO client (id, name, surname, surname2, dob, phone, email, address) 
               VALUES (NVL(rec.id, iOffset), rec.cl_name .....   );
        iOffset := iOffset + 1;    
    end loop;
end;

EDIT Another approach would be to find the max value i purchases and add the rownum to get a unique id. Try this, not tested...

INSERT INTO client (id, name, surname, surname2, dob, phone, email, address) 
SELECT DISTINCT NVL(cl_dni, mv.val + rownum), cl_name, cl_surn1, cl_surn2, cl_birth, cl_phone, cl_email, cl_address 
FROM purchases, 
    (select max(cl_dni) val from purchases) mv; 

MySQL INSERT IGNORE Statement Explained By Examples, When you use the INSERT statement to add multiple rows to a table and if an id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(50) NOT NULL UNIQUE );. The UNIQUE constraint ensures that no duplicate email exists in the email INSERT IGNORE INTO subscribers(email) VALUES('john.doe@gmail. com'),� For this example, we will use the following table with duplicate PK values. In this table the primary key is the two columns (col1, col2). We cannot create a unique index or PRIMARY KEY constraint since two rows have duplicate PKs. This procedure illustrates how to identify and remove the duplicates.

How to INSERT If Row Does Not Exist (UPSERT) in MySQL, Learn how to INSERT an If Row Does Not Exist (UPSERT) in MySQL. it is necessary to INSERT rows after determining whether that row is, in fact, new or already exists. statement which contains a duplicate value in a UNIQUE index or PRIMARY KEY field does not produce an mysql> SELECT * FROM books LIMIT 3;� However the primary key itself will not protect you from user generated or user captured data that may contain duplications. Enter the “Unique” constraint! This is a very powerful table-level constraint that you can apply to your table against a chosen table column, which can greatly assist to prevent duplicates in your data.

How to Skip Duplicate Key Errors (ORA-00001) in Oracle Database , select *. from massive_table. where last_row_is_a_duplicate = 'Y' ; But first, a quick recap on what primary keys and unique constraints are and not null. ); insert into accounts ( username, given_name ). values to contain key values that are already in the table, it's best to avoid where not exists (. Create Course Table without Primary Key. In order to reproduce the scenario in which duplicate records in the absence of a primary key fall into a table, let us first create a new Course table without any primary key in the University2 database as follows:

MySQL - Handling Duplicates, You can use a PRIMARY KEY or a UNIQUE Index on a table with the To prevent multiple records with the same first and last name values from being created in does not error out and at the same time it will not insert duplicate records as well. mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM� There are multiple ways to remove duplicate records in SQL Server. In this tip, I'll use the SSIS Sort Transformation to remove records and show you how easy it can be. The SSIS Sort Transformation task is useful when you need to sort data into a certain sort order. You can compare it to the ORDER BY clause in a SELECT statement.

Comments
  • What database engine are you using for this? Make the database AUTO INCREMENT these values, in that case you would not have to insert the id when you insert a new record because the database engine handles it for you. The unique constraint violation tells you that this id already exists, which means you have given the column the unique constraint. Why haven't you made the column (something like a) PRIMARY KEY AUTO INCREMENT (exact code depends on database engine)?
  • You do not compare to your newly calculated values at all.
  • I am copying these values from another table that is not mine. Each client has a random 8 digit id, so I actually need to insert the ids.
  • @PM77-1 i know i should ideally compare and i will definitely implement that. there is only one row in the original database where client doesn't have an ID so at this point I'm just assuming that it is not likely that I will get a value that already exists in the database. Also I tried hardcoding a number that I know doesn't exists in the database and still got the unique constraint violation so I think the main problem is the WHERE NOT EXISTS part. I will update the query with comparison, thanks!
  • The only unique constraint on the client table is Primary Key. There are dubs in the purchases table because some clients made several purchases so the columns I am trying to insert into the new table (client info) are the same for those rows.
  • The problem is not the random numbers, even if I replace the random number part with a predefined value that doesn't exist in the original database it gives a unique constraint violated error.
  • But as I fill the table doesn't it NOT EXIST compare the values against the values I just inserted?
  • Dont think so, the selected records will be inserted in a single step. So the records in the client table will not be visible before after the insert.
  • That makes sense. I am doing this for a school project and we haven't covered PLSQL, so I don't I can use it but thanks anyway! I will try to see if I can figure out a way of doing this in SQL.
  • Try the Edit approach in that case.