File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL duplicate data into a new PK Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL duplicate data into a new PK" Watch "SQL duplicate data into a new PK" New topic
Author

SQL duplicate data into a new PK

Dylan Margoczi
Ranch Hand

Joined: Jul 12, 2007
Posts: 38
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
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
You could use a cursor for going through all the records


Cursors


Remko (My website)
SCJP 1.5, SCWCD 1.4, SCDJWS 1.4, SCBCD 1.5, ITIL(Manager), Prince2(Practitioner), Reading/ gaining experience for SCEA,
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Jul 12, 2007
Posts: 38
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

Joined: Oct 06, 2007
Posts: 338
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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: SQL duplicate data into a new PK
 
Similar Threads
Strange JDBC transaction problem
Row count for an update statement
JDBC Callable Statement JConnect2 Returning null resultset
Restore problem
Closing cursor in a stored procedure when invoked from a callable statement