• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL duplicate data into a new PK

 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)



Thanks in advance
 
Ranch Hand
Posts: 893
Tomcat Server Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You could use a cursor for going through all the records


Cursors
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
And then we all jump out and yell "surprise! we got you this tiny ad!"
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic