Take the 2-minute tour ×

I need to insert data to new table which the fields are constructed from two existing tables named Table1 and Table2 as below. The path1 and path2 are from Table2 but the references are stored in Table1. Table1 should not be altered, because Table2's path field will be changed.

New Table
id path1 path2
1  /a/b  /a/c
2  /a/d  /a/e

id path1_id path2_id
1  1        2
2  3        4

id path
1  /a/b
2  /a/c
3  /a/d
4  /a/e
share|improve this question
I did searches but couldn't find what I need exactly. And, forgot to mention, I need the query for mysql, thanks. –  babyromeo Apr 10 '11 at 16:55
Supply the create statement for each table please. –  Khez Apr 10 '11 at 16:57
add comment

1 Answer

up vote 0 down vote accepted

You didn't give us much to work with, so here is an attempt just to get the boll rolling:

  into new_table(id, path1, path2)
select t1.id
  from table1 t1
  join table2 p1 on(p1.id = t1.path1_id)
  join table2 p2 on(p2.id = t1.path2_id)

...based on the following assumptions about your data:

  • table1.id is a unique key
  • table2.id is a unique key
  • new_table.id "inherits" the key from table1
  • There is a (trusted or enforced) foreign key constraint from table1.path1_id => table2.id
  • There is a (trusted or enforced) foreign key constraint from table1.path2_id => table2.id
share|improve this answer
Thanks. I'm sure it just a piece of cake for an expert. –  babyromeo Apr 11 '11 at 4:55
add comment

Your Answer


