Q:

how to I replace a column value with the newest id after copying some columns to a new table?

I have a MySQL table like this
id parentid name
-- ------ ----
1    0      name1
2    1      name2
3    2      name3
4    0      name4
.    .       ....

After copying some rows to a new table I need to replace the parentid values to newly inserted ids if it is not 0 like below
ids parentids names
-- ------     ----
10     0      name1
11    10      name2
12    11      name3
13     0      name4
.  .          ....

How can I establish this using PHP and MySQL? Thank you.

A:

You should probably do whatever you can to keep the IDs the same. If this is unavoidable, you'll have to remap them:
CREATE TEMPORARY TABLE _remap AS
  SELECT table_b.id AS new_id, table_a.id AS old_id
    FROM table_b
    LEFT JOIN table_a ON table_a.name=table_b.name

UPDATE table_b,_remap SET table_b.parent_id=_remap.new_id
  WHERE table_b.parent_id=_remap.old_id

This presumes your name column is unique.

