SQL duplicate data into a new PK

[Dylan Margoczi]

Hi.
I'm not sure if I can ask this question here but I guess I'm going to any way.

It's basically a procedure I'm doing in MySQL for my program.
I'd like to copy rows in a table but I need to change the values in a certain field as they need to be unique.

I get the unique keys from another table where I hold the next unique key to be used and use a custom function to increment it.

I copy the rows needed into a temporary table where i can work with it and change the values, I am hoping there is a way of going through each row and giving each one it's new unique key, I wanted to do it through a while loop. Will this work in any way? (my only problem is I don't know how to go from one row to another)

CREATE PROCEDURE COPYTABLE_PROC(IN NEW_VAL VARCHAR(),IN OLD_VAL VARCHAR())
BEGIN
    DROP TEMPORARY TABLE IF EXISTS TEMP_TABLE1;
    CREATE TEMPORARY TABLE TEMP_TABLE1 SELECT * FROM TABLE1 WHERE FIELD1 = OLD_VAL;
    UPDATE TEMP_TABLE1 SET FIELD1 = NEW_VAL WHERE FIELD1 = OLD_VAL;
    
    SELECT CONVERT(ROW_COUNT(), DECIMAL) INTO @ROWCOUNT;
    SET @COUNT = 0;
    
    WHILE @COUNT < @ROWCOUNT DO
          SET @COUNT = @COUNT + 1;
          SELECT KEY1 INTO @UNIKEY FROM KEYTABLE WHERE KEYNAME = 'TABLE1';
          UPDATE TEMP_TABLE1 SET FIELD1 = @UNIKEY WHERE # *** HERE IS WHERE I WOULD CHECK WHICH ROW I'M ON
          UPDATE KEYTABLE SET KEY1 = AUTOINC_FUNC(@UNIKEY) WHERE KEYNAME = 'TABLE1'; # THE AUTOINC_FUNC FUNCTION AUTO INCREMENTS THE KEY1 VALUE
    END WHILE;
      
    INSERT INTO TABLE1 (SELECT * FROM TEMP_TABLE1 WHERE FIELD1 = NEW_VAL);
END;

Thanks in advance

[Remko Strating]

You could use a cursor for going through all the records


Cursors

[Paul Campbell]

Hi Dylan,

You're basically putting the "cart before the horse".

This issue is best resolved in your temporary table's ddl by using the MySQL AUTO_INCREMENT attribute to generate a unique value for new rows:

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

SELECT * FROM animals;
Which returns:

+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+

If there is some reason you can not alter the table... use a variable to set your max-key and set its value to the max(old_value) in your old table + 1 (or some number of your choosing).

[Dylan Margoczi]

Thanks Remko,
I used cursors and all is working perfectly now.
Thanks a lot.

Paul, I'm not 100% sure on what you are trying to show me there.
But from what I pick up, I can't do that because I want it to be Dynamic in a way that I can add columns to the tables later on and not have to worry about adding it in to the procedure.
I also don't like using SQL's auto increment because I find it too hard to maintain in my program. My values that need to be incremented have strings in them too and so I had to create my own function.

Thanks guys.

[Paul Campbell]

Originally posted by Dylan Margoczi:
Thanks Remko,
I used cursors and all is working perfectly now.
Thanks a lot.

Paul, I'm not 100% sure on what you are trying to show me there.
But from what I pick up, I can't do that because I want it to be Dynamic in a way that I can add columns to the tables later on and not have to worry about adding it in to the procedure.
I also don't like using SQL's auto increment because I find it too hard to maintain in my program. My values that need to be incremented have strings in them too and so I had to create my own function.

Thanks guys.

Hey Dylan,

I think I completely misinterpreted your issue... my mistake. I'm glad the issue is resolved.