2

I am able to clone a row in my table with the following code:

$uuid = 'blahblah';    
INSERT INTO events (title, subtitle) SELECT title, subtitle FROM events WHERE uuid = '$uuid'

However, I want to generate a new unique ID for the newly cloned row. The name of this table column is "uuid" How do I insert a new unique ID during the cloning process? I have an auto index columnt of type INT but I also have a unique ID column of type VARCHAR for each row (mixed alpha numeric).

Thanks in advance.

Freddie
  • 691
  • 2
  • 9
  • 23
  • `$uuid = 'blahblah';` does not seem like MySQL. If you are using PHP, this insert query is likely prone to an SQL injection. – undefined Jan 06 '21 at 11:03

2 Answers2

3

I found the answer here - PHP MySQL Copy a row within the same table... with a Primary and Unique key

Basically, select everything except the ID and as long as the primary key is set to auto increment - shouldn't be a problem.

Community
  • 1
  • 1
Freddie
  • 691
  • 2
  • 9
  • 23
  • Your question wasn't about PHP, neither is PHP in the tags. What about a solution with MySQL only? The answer provided there is obvious and also requires 2 queries: select then insert, which with prepared statements would be about 10 lines alone. – undefined Jan 06 '21 at 11:01
  • @undefined You clearly only read the title of this link. If you actually did, you would see that the accepted solution is a MySQL solution. – Freddie Jan 07 '21 at 05:03
0
/*
eduardoaf.com
https://github.com/eacevedof/
*/
DELIMITER $$

DROP PROCEDURE IF EXISTS `prc_clone_row`$$

CREATE PROCEDURE `prc_clone_row`(
    sTableName VARCHAR(25)
    ,sId VARCHAR(5)
    )
BEGIN
    SET @sSQL := CONCAT('SELECT (MAX(id)+1) AS idnew FROM ',sTableName,' INTO @sIdNew');
    PREPARE sExecute FROM @sSQL;
    EXECUTE sExecute;

    IF (@sIdNew IS NOT NULL) THEN
        SET @sSQL := CONCAT('CREATE TEMPORARY TABLE tempo_table SELECT * FROM ',sTableName,' WHERE id = ',sId,'; ');
        PREPARE sExecute FROM @sSQL;
        EXECUTE sExecute; 

        SET @sSQL := CONCAT('UPDATE tempo_table SET id=',@sIdNew,' WHERE id=',sId,'; ');
        PREPARE sExecute FROM @sSQL;
        EXECUTE sExecute;        

        SET @sSQL := CONCAT('INSERT INTO ',sTableName,' SELECT * FROM tempo_table WHERE id=',@sIdNew,'; ');
        PREPARE sExecute FROM @sSQL;
        EXECUTE sExecute; 

        SET @sSQL := CONCAT('SELECT * FROM ',sTableName,' ORDER BY id DESC;');
        PREPARE sExecute FROM @sSQL;
        EXECUTE sExecute;   
    ELSE
        SELECT CONCAT('TABLE ',sTableName,' IS EMPTY!!!') AS msg;
    END IF;

END$$

DELIMITER ;

CALL prc_clone_row('app_cms_content','1');
ioedu
  • 27
  • 8