MySQL INSERT INTO SELECT JOIN [英] MySQL INSERT INTO SELECT JOIN
问题描述
我正在尝试将旧的 MySQL 基础修改为新的 - 我希望更好.所以我有一个问题,因为我无法自己处理:
I'm trying to modify old MySQL base to new - I hope better. So I've got a question because I can't handle it myself:
我有一个有 5 列的旧表:
I've got an old table which has 5 columns:
*table_old*
id | name_1 | name_2 | name_3 | role
1 | aaa | bbb | ccc | g
2 | aaa | ccc | ddd | f
3 | bbb | aaa | ddd | g
等等.
我创建了一个新表来存储 2 列的名称值,它看起来像这样:
I made a new table to store name values with 2 columns and it looks like that:
*table_name*
id | name
1 | aaa
2 | bbb
3 | ccc
4 | ddd
因为我使用角色命名新表(旧表的最后一列),所以我可以创建具有 4 列的新表:
Because I'm naming new tables using it role (last column from old table) I can create new table with 4 columns so:
*table_g*
id | name_1 | name_2 | name_3
1 | aaa | bbb | ccc
2 | bbb | aaa | ddd
*table_f*
id | name_1 | name_2 | name_3
1 | aaa | ccc | ddd
另外,因为名称可能很长,我更愿意使用表 2 中的 id 值而不是名称 - 我想得到类似的东西:
Also because the name can be very long I would prefer to use id value from table 2 instead name - I would like to get something like that:
*table_g*
id | id_1 | id_2 | id_3
1 | 1 | 2 | 3
2 | 2 | 1 | 4
*table_f*
id | id_1 | id_2 | id_3
1 | 1 | 3 | 4
我将列的名称从 *name_1* 更改为 *id_1* 等
I changed name of columns from *name_1* to *id_1* etc.
现在我在寻求帮助.我正在尝试使用类似的东西:
Now I'm asking for help. I'm trying to use something like that:
INSERT INTO `table_g`(`id_1`, `id_2`, `id_3`)
SELECT `table_name`.`id`,`table_name`.`id`,`table_name`.`id`
FROM `table_name`
RIGHT JOIN `table_old` ON `table_name`.`name` = `table_old`.`name_1`
RIGHT JOIN `table_old` ON `table_name`.`name` = `table_old`.`name_2`
RIGHT JOIN `table_old` ON `table_name`.`name` = `table_old`.`name_3`
WHERE `table_old`.`role` = 'g';
但这不起作用......如果可以,请帮助:/
But this doesn't work... Please help if you can :/
推荐答案
INSERT INTO `table_g`(`id_1`, `id_2`, `id_3`)
SELECT t1.`id`, t2.`id`, t3.`id`
FROM `table_name` tn
RIGHT JOIN `table_old` t1 ON tn.`name` = t1.`name_1` AND t1.`role` = 'g'
RIGHT JOIN `table_old` t2 ON tn.`name` = t2.`name_2` AND t2.`role` = 'g'
RIGHT JOIN `table_old` t3 ON tn.`name` = t3.`name_3` AND t3.`role` = 'g'
乍一看,您的选择中存在很多歧义,因此至少您应该尝试使用上面的代码会发生什么.
At first sight there's much ambiguity in your select, so at least you should try what happens using the code above.
这篇关于MySQL INSERT INTO SELECT JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!