1

I have a set of rows in Table A (with AI PK) that I'd like to clone based on certain criteria, ie:

INSERT INTO A (field1, field2, field3) SELECT field1,field2,field3 FROM A WHERE (criteria)

But, these rows have a 1-1 relation with Table B which has an AI PK in B and FK in A. I would like to also clone the related rows in table B and have the cloned rows in A point to the cloned rows in B not the original rows.

I'm looking for the simplest way to do this--preferably just in SQL.

Here's an example of what I'm trying to do:

BEFORE CLONE:

Table A 
ID   B_FK   Other Data  Meets Clone Criteria    
1    101    Data 1      true
2    102    Data 2      false 
3    103    Data 3      true
4    104    Data 4      true

Table B
ID   Other Data
101  Data A
102  Data B
103  Data C
104  Data D

AFTER ROW CLONING:

Table A 
ID   B_FK   Other Data
1    101    Data 1
2    102    Data 2 
3    103    Data 3
4    104    Data 4
5    105    Data 1
6    106    Data 3
7    107    Data 4

Table B
ID   Other Data
101  Data A
102  Data B
103  Data C
104  Data D
105  Data A
106  Data C
107  Data D
scotru
  • 2,563
  • 20
  • 37
  • This question addresses the same problem in SQL Server--but I'm stuck in MySQL: http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id – scotru Nov 11 '15 at 00:23
  • Here's a related question with a stored procedure to do a single row--but I'd like to do a whole group of rows: http://stackoverflow.com/questions/6767066/copy-row-and-all-of-its-children – scotru Nov 11 '15 at 00:30

2 Answers2

2

Here's a way using a subquery to duplicate the rows in table b and have them point to the new fk in table a

insert into b (a_fk, some_field)
select 
    (select max(a2.id) from a a2
    where a2.id <> a1.id
    and a2.field1 = a1.field1
    and a2.field2 = a1.field2
    and a2.field3 = a1.field3), 
    b.some_field
from b
join a a1 on a1.id = b.a_fk
where (criteria)

But it's probably easier (and faster) to create an additional column containing the source id

insert into a (field1, field2, field3, source_id) 
select field1, field2, field3, id 
from a where (criteria)

insert into b (a_fk, some_field)
select a.id, b.some_field 
from b
join a on a.source_id = b.a_fk
where (criteria)
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
  • There's so many columns in the table that the first approach isn't too appealing (I'd also like a more reusable solution). Adding a column as you suggest may indeed be a good solution. I think this could also be done with a MySQL cursor? I'm trying to explore that approach now. – scotru Nov 11 '15 at 07:22
  • I used the extra column approach – scotru Nov 11 '15 at 17:26
1

SET FOREIGN_KEY_CHECKS=0 then do whatever you need to with INSERT INTO ... SELECT if there is more to do afterwards that needs foreign key checks SET FOREIGN_KEY_CHECKS=1

Sasha Pachev
  • 5,162
  • 3
  • 20
  • 20
  • The problem is I need the cloned rows in TABLE A to have the correct foreign keys for the newly cloned rows in TABLE B. – scotru Nov 11 '15 at 00:21
  • Can you clarify this with an example? In particular, why just inserting the rows from A into B will not work. – Sasha Pachev Nov 11 '15 at 01:30
  • Yes, I've edited the question above. I'm not trying to clone rows from A to B. I'm trying to clone rows in A to A while also cloning the related child rows in B to B. – scotru Nov 11 '15 at 07:08