copying some of recursive records

[dotJoon]

 data in myTable

(ID) category area
(1)      1    world 
(2)      1    Asia 
(3)      1    Europe 
(4)      3    France 
(5)      2    Japan 
(6)      3    England  
(7)      4    Paris  
(8)      2    China 
(9)      8    Beijing

I have the above data in myTable.

I like to copy a record and put it into another area.

For example, let's suppose the object record for copying is "(2) Asia,"
and target area is "(4) France."

then,
I can make a new record "(10) 4 Asia."
This means the new record "Asia" is belongs to "France."
The whole table will be like the following.

 new data in myTable

(ID) category area
(1)      1    world 
(2)      1    Asia 
(3)      1    Europe 
(4)      3    France 
(5)      2    Japan 
(6)      3    England  
(7)      4    Paris  
(8)      2    China 
(9)      8    Beijing 
(10)     4    Asia

It's not difficult to make the new record.


Now I am telling what I want.
I like to copy not only the object record but also all records which are belongs to the object record.

So my target table would be like the following.

 target data in myTable

(ID) category area
(1)      1    world 
(2)      1    Asia 
(3)      1    Europe 
(4)      3    France 
(5)      2    Japan 
(6)      3    England  
(7)      4    Paris  
(8)      2    China 
(9)      8    Beijing 
(10)     4    Asia
(11)    10    Japan 
(12)    10    China  
(13)    12    Beijing  

Thanks in advance

[kleineme]

Hi,

I can't think of any way to do this with MySQL only. You would have to write a piece of code in the programming/scripting language if your choice.