1

Salute! I use following raw sql statement in Symfony to insert large number of rows from existing table INTO other table with the same structure, what contains as well a large number of existing rows [because the used tables are dynamically created to runtime, there are NOT SYMFONY-ENTITY]:

$sql_insert = "INSERT INTO table1".$proid." SELECT * from ".$tablename." ON DUPLICATE KEY UPDATE table1".$proid.".id = table1".$proid.".id + 1";

and still getting the same error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2' for key 'PRIMARY'

It doesn't work for every entry, just only for first inserted entry... Please help me to resolve this problem. How can i update the primary key?

lxg
  • 12,375
  • 12
  • 51
  • 73
Ifgenia
  • 29
  • 1
  • 6
  • 1
    This has nothing to do with Symfony, it is a basic MySQL error. Could you please add the schema of both tables to your post? – lxg Aug 15 '18 at 18:01
  • The complete schema is unfortunatelly to long for posting. Both tables have the same schema and 80 cols, all are numerated from a , b ... ca CREATE TABLE `pro_nr_122` ( `id` int(11) NOT NULL, `idofma` int(11) DEFAULT NULL, `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `a` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `b` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, ... – Ifgenia Aug 15 '18 at 20:12
  • The reason you are getting this is because you are attempting to take all the columns. It is very likely that both tables have system generated key values, so what it's saying is the primary key value '2' already exists in the target table and you are attempting to put the key value from the source table into it. If you want to do this, you will need to list ALL the "into" columns and all the "from" columns and exclude the primary key from the selected-from, or source, table. – T Gray Aug 15 '18 at 20:16
  • @T Gray: yes, i know the reason, why i get this error, its not likely, it is fact, the keys are setted by autogenerate. I think, in worst case i need foreach with the result, and insert every row from other table, but its realy not the best solution :( or i try to overwrite with ON DUPLICATE KEY UPDATE, but i have 80 columns, its the same input of time i think – Ifgenia Aug 15 '18 at 20:27
  • 1
    If I have two tables, table1(id int auto, somechar varchar(30)) and table 2(id int auto, someotherchar varchar(30)) and I want to avoid the problem, I "insert into table1(somechar) select someotherchar from table2;" If I want to preserve the original id, i create another column in table1 "archiveid int" (no autogen) and my insert is "insert into table1(archiveid, somechar) select id, someotherchar from table2;" That completely obviates the duplicate key problem Either solution works. Basically, your "select *" is the problem. – T Gray Aug 15 '18 at 20:31

2 Answers2

1

Symfony helped to resolve the duplicate key problem, like this:

$conn = $this->getDoctrine()->getManager()->getConnection();    
$sql_insert = " SELECT * from ".$tablename."";
            $stmt = $conn->prepare($sql_insert);
            $stmt->execute();
            $result = $stmt->fetchAll();

            foreach($result as $item => $row) {
                $dataArray1 = array(
                    'a' => $row['a'], 
                    'b' => $row['b'], 
                    ...);

                    $conn->insert('pro'.$id.'', $dataArray1);
                }
Ifgenia
  • 29
  • 1
  • 6
-2

The correct query is : 

INSERT INTO table(column_list)
VALUES(value_list)
ON DUPLICATE KEY UPDATE column_1 = new_value_1, column_2 = new_value_2, …

You need to update key for each column.

You can reference this issue from the following links, hope it will solve the question:

  1. On Duplicate Key Update same as insert

  2. https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Nabil Afraz
  • 77
  • 4