Save new id into old table
I want to move data from these old tables
restaurant_id | restaurant_nm | more data
bar_id | bar_nm | more data
To
venue_id开发者_如何学C | venue_nm
I'll add field venue_id to the old tables
Then I want to run a query similar to this:INSERT INTO `venue` (SELECT null, `restaurant_nm` FROM `restaurant`)
However, while do the copy I want the new id to be stored into the old table. Is this possible with pure mysql?
Edit The old restaurants can be chains (multiple messy joe's), the only thing that identifies them 100% is the id
You could temporarily store the old ID in the new table (in an extra column) and then do an UPDATE on the old table. That's two lines of 'pure SQL.'
restaurant_id |restaurant_name | v_id
venue_id | venue_name | rest_id
INSERT INTO `venue` (SELECT null, `restaurant_nm`, `restaurant_id` FROM `restaurant`)
and then
UPDATE restaurant r
INNER JOIN venue v
ON r.restaurant_id = v.rest_id
SET r.v_id = v.venue_id
Interested to see what a more elegant solution might be.
精彩评论