[SOLVED] Sql Statement: Insert On Key Update is not working as expected when primary key is not specified in the fields to insert

Sql Statement: Insert On Key Update is not working as expected when primary key is not specified in the fields to insert

Hello I am using the "INSERT ON DUPLICATE KEY UPDATE" sql statement to update my database.

All was working fine since I always inserted an unique id like this:

INSERT INTO devices(uniqueId,name) 
    VALUES (4,'Printer') 
    ON DUPLICATE KEY UPDATE name = 'Central Printer';

But for now, I need to insert elements but I don't insert a unique id, I only insert or update the values like this:

INSERT INTO table (a,b,c,d,e,f,g)
    VALUES (2,3,4,5,6,7,8) 
    ON DUPLICATE KEY
        UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;

Have to say that an autoincrement primary key is generated always that I insert a row.

My problem is that now the inserted rows are duplicated since I don't insert the primary key or unique id explicitly within the sql statement.

What I am supposed to do?

For example, maybe I need to insert the primary key explicitly? I would like to work with this primary autoincremented key.

For recommendation from Gordon I am adding a sample case the you can see in the next image Rows Output

In this case I add the first three rows, and then I try to update the three first rows again with different information.... Ok I am seeing the error... There is no key to compare to...... :$

Thanks for your answers,

Solution

If you want to prevent columns from being duplicated, then create a unique index or constraint on them. For instance:

create unique index unq_table_7 on table(a, b, c, d, e, f, g);

This will guarantee that the 7 columns -- in combination -- are unique.