当前位置：网站首页>【MySQL】 insert into ... select ...

【MySQL】 insert into ... select ...

2022-07-21 12:04:00 【sayyy】

The table structures of the two tables are the same

insert into sys_menu 
select *
from sys_menu_back
where menu_id=2092;

When copying data from the same table

insert into sys_menu 
select *
from sys_menu
where menu_id=2092 or parent_id=2092;

Report errors ：ERROR #1062

1. Concatenate all column names of the table into strings

SELECT GROUP_CONCAT(CONCAT('`', `column_name`, '`') ORDER BY `ordinal_position`) 
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() AND `table_name`='sys_menu';

2 To write insert into … select …

insert into `sys_menu` (`menu_name`,`parent_id`,`order_num`,`url`,`target`,`menu_type`,`visible`,`is_refresh`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
select `menu_name`,`parent_id`,`order_num`,`url`,`target`,`menu_type`,`visible`,`is_refresh`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`
from `sys_menu`
where `menu_id`=2092;