December 8th, 2013, 03:00 PM
How do I duplicate rows?
I have difficulties solving a problem. I'll try to explain the problem the best I can.
I got two tables: Table A and Table B. Due to some poor programming the tables are not normalized and this is what's causing my problem.
- Field A ID (Unique)
- TableB.FieldA (Foreign key)
- Field X
- Field Y
- Field z
(Field A and Table B ID is a joint primary key)
- Field A (Old unique key)
- Field B (Foreign key)
- Field C (new unique key)
(Field A and Field B ID is a joint primary key)
Due to poor programming Field A and Field B were used as a joint primary key, but conceptually only Field A should be a primary key. Because of that the it sometimes happen that the same key in field A appears multiple times so I had to make a new primary key in Field C with unique keys.
Table A uses TableB.FieldA as a foreign key. Multiple rows in Table A can have the same Foreign Key. But because I had to make a new unique key in Table B (FieldC), those data in Table A with the same foreign key also belongs to the new keys made in TableB.FieldC.
As you see in the first quote ID 1 appears 3 times as Foreign key in TableA.FieldB. In Table B you see that ID 1 got two new ID's (9000 and 9001). That means that for every row in Table A with the old ID 1 I have to insert two new rows for ID 9000 and 9001. And that's my problem. How do I duplicate the rows in Table A based on the number of new ID's made in TableB.FieldC?
Don't be afraid ask question to clarify the problem if needed. It was difficult to write down the problem so I don't know if I got the problem well explained. I want the result to be like in the code-quote below:
December 10th, 2013, 01:30 PM
The question is how you are planning to link tables if
1..........701.......9001 (ID 1 appears 2 times in Field A. Not allowed)
old key 1 should be linked to first 2 records from Table B
why not just simple join table as is?
select A.*, B.FieldA as FieldAFromB
from TableA as A
(select distinct FieldA, FieldB
) as B
A.FieldB = B.FieldA
December 10th, 2013, 02:36 PM
I would recommend just redup table and set Field A as unique key, so system not let insert duplicate key