#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    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.



    Table A:
    - Field A ID (Unique)
    - TableB.FieldA (Foreign key)
    - Field X
    - Field Y
    - Field z
    etc
    (Field A and Table B ID is a joint primary key)


    Table B:
    - 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.

    Example:
    Table B

    Field A Field B Field C
    ------- ------- -------
    1..........700.......9000
    1..........701.......9001 (ID 1 appears 2 times in Field A. Not allowed)
    2..........700.......9002
    3..........700.......9003


    Table A
    Field A Field B (from TableB.FieldA) Field C
    ------- ---------------------------- ---------
    1..........1........................................Some text
    2..........1........................................Some text
    3..........1........................................Some text
    4..........2........................................Some text
    5..........2........................................Some text
    6..........3........................................Some text
    7..........3........................................Some text
    - 1 -
    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:
    Table A
    Field A Field B (from TableB.FieldA) Field C
    ------- ---------------------------- ---------
    1..........1........................................Some text
    2..........1........................................Some text
    3..........1........................................Some text
    4..........9001...................................Some text
    5..........9001...................................Some text
    6..........9001...................................Some text

    7..........2........................................Some text
    8..........2........................................Some text
    9..........3........................................Some text
    10.........3........................................Some text

    Rows in bold are new rows
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    The question is how you are planning to link tables if
    1..........700.......9000
    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?

    Code:
    select A.*, B.FieldA as FieldAFromB
    from TableA as A
    	inner join 
    	(select distinct FieldA, FieldB
    	from TableB 
    	) as B
    	on 
    	A.FieldB = B.FieldA
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    I would recommend just redup table and set Field A as unique key, so system not let insert duplicate key

IMN logo majestic logo threadwatch logo seochat tools logo