开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜