ON DUPLICATE KEY doesn't work with my INSERT INTO script

Question

I have two tables. One is permission_index and the other is permission_index_bkp.

My script is supposed to transfer some rows and data from permission_index to permission_index_bkp. However, I don't want to create new rows if the data in all 4 columns is the same.

This is what I am trying to use:

INSERT INTO permission_index_bkp
  (b_app, b_perm_type, b_perm_type_id, b_perm_3)
  SELECT app, perm_type, perm_type_id, perm_3
    FROM permission_index
   WHERE app = 'module'
     AND perm_type = '1'
     AND perm_type_id = '2'
     AND perm_3 IS NOT NULL 
      ON DUPLICATE KEY UPDATE app = app,
                              perm_type = perm_type, 
                              perm_type_id = perm_type_id,
                              perm_3 = perm_3;

I've been getting errors like:

1052: Column 'app' in field list is ambiguous (I think I fixed this by changing columns' name in permission_index_bkp).

or

1054: Unknown column 'app' in 'field list'

Could you guys tell me how to fix my query?

Thanks!!

EDIT 1:

This has made the errors disappear, however, rows keep being added in permission_index_bkp, even though they have the same values:

INSERT INTO permission_index_bkp
  (b_app, b_perm_type, b_perm_type_id, b_perm_3)
  SELECT app, perm_type, perm_type_id, perm_3
    FROM permission_index AS pi
   WHERE app = 'module'
     AND perm_type = '1'
     AND perm_type_id = '2'
     AND perm_3 IS NOT NULL 
      ON DUPLICATE KEY UPDATE b_app = b_app,
                              b_perm_type = b_perm_type, 
                              b_perm_type_id = b_perm_type_id,
                              b_perm_3 = b_perm_3;

Answer 1

Though I never used this syntax but after doing some search in google I found that you need to declare an unique key on all four columns of your permission_index table to remove duplicate entries.

Please check below example:

 create table test (a int, b int);
 insert into test values(1,1);
 insert into test values(1,2);

 create table test2 (a int, b int,
 UNIQUE KEY uktest2 (a,b));

 INSERT INTO test2
   (a,b)
   SELECT a,b
     FROM test b
    WHERE a=1
       ON DUPLICATE KEY UPDATE a = b.a,
                               b=b.b;


 select * from test;
 select * from test2;

Output:

table test:

a	b
1	1
1	2

table test2:

a	b
1	1
1	2

 insert into test values(1,3);
 insert into test values(1,1);


 INSERT INTO test2
   (a,b)
   SELECT a,b
     FROM test b
    WHERE a=1
       ON DUPLICATE KEY UPDATE a = b.a,
                               b=b.b;


 select * from test;
 select * from test2;

Output:

table test:

a	b
1	1
1	2
1	3
1	1

table test2:

a	b
1	1
1	2
1	3

db<fiddle here

Answer 2

Use VALUES() in the ON DUPLICATE KEY clause to specify that you want to update a column to the value that would have been inserted.

And you need to be assigning to the column names in the destination table, not the names in the table you're selecting from, so they should all be b_XXX.

INSERT INTO permission_index_bkp
  (b_app, b_perm_type, b_perm_type_id, b_perm_3)
  SELECT app, perm_type, perm_type_id, perm_3
    FROM permission_index
   WHERE app = 'module'
     AND perm_type = '1'
     AND perm_type_id = '2'
     AND perm_3 IS NOT NULL 
ON DUPLICATE KEY UPDATE 
    b_app = VALUES(b_app),
    b_perm_type = VALUES(b_perm_type), 
    b_perm_type_id = VALUES(b_perm_type_id),
    b_perm_3 = VALUES(b_perm_3);

Also, you don't need to include the unique key column(s) in the ON DUPLICATE KEY UPDATE clause.