Copy some records in the same table in mySQL-CodePudding

I would like to copy some records in the same table for new user (75) with incrementing id in mySQL. ID is not an autoincrement.

content of table is :

 ID       USER      col1    col2  
 67       60        toto1
 68       60        toto2           
 69       60        toto3
 70       60        toto4
 71       60        toto5
 72       60        toto6
 81       60        toto7
 82       60        toto8

what i expect in the same table :

 ID       USER      col1    col2  
 67       60        toto1
 68       60        toto2           
 69       60        toto3
 70       60        toto4
 71       60        toto5
 72       60        toto6
 81       60        toto7
 82       60        toto8
 83       75        toto1
 84       75        toto2                       
 85       75        toto3
 86       75        toto4
 87       75        toto5
 88       75        toto6
 89       75        toto7
 90       75        toto8

below is my code but i get error : SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE

  BEGIN
  DECLARE RecordsToImport INTEGER;
          RecordsImported INTEGER;
          totRecordsToImport INTEGER;         
        EXECUTE IMMEDIATE ('TRUNCATE TABLE TMP1');
        EXECUTE IMMEDIATE ('TRUNCATE TABLE TMP2');          
        INSERT INTO TMP1 SELECT * FROM table1 WHERE user=60; -- old user
        UPDATE TMP1 SET user=75 WHERE user = 60; -- TEST NEW USER
        SET RecordsToImport =   (SELECT COUNT(*) FROM TMP1) ;
        SET totRecordsToImport = RecordsToImport;       
        WHILE RecordsToImport > 0 DO
                INSERT INTO TMP2  SELECT * from TMP1 order by ID asc LIMIT 1;
                DELETE FROM TMP1 WHERE ID in ( SELECT ID FROM TMP2 );
                BEGIN
                    INSERT INTO table1 SELECT (SELECT MAX(ID)   1 FROM table1 ),user, col1, col2, col3, col4 FROM TMP2 ;
                    SET RecordsImported =   (SELECT COUNT(*) FROM TMP2) ;
                    SELECT CONCAT('Added: ', RecordsImported, ' row(s) of ', totRecordsToImport );
                    EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP2';
                    SELECT COUNT(*) INTO RecordsToImport FROM TMP1;
                    SELECT CONCAT('Remaining: ', RecordsToImport, ' row(s) of ','-------------' );
                END;        
        END WHILE;
END;

CodePudding user response：

If ID is AUTOINCREMENT;

INSERT INTO table
SELECT
75,col1, col2
FROM table
WHERE USER = 60

CodePudding user response：

The basic idea would be to insert-select from the same table.

If the id is defined as auto_increment, just leave it out of the query and let MySQL do its thing:

INSERT INTO mytable (user, col1)
SELECT 75, col1
FROM   mytable
WHERE  user = 60

If it isn't, you can manually increment it:

INSERT INTO mytable (id, user, col1)
SELECT id   8, 75, col1
FROM   mytable
WHERE  user = 60