1.错误描述
MariaDB 10.5.9 在使用 INSERT INTO SELECT 时会报错，错误如下：
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server
version for the right syntax to use near 'SELECT birds.
bird_id, humans.human_id,date_spotted, gps_coordinates
F...' at line 4
2.原因
这主要是因为新版本的 MariaDB 的命令与 MySQL 的有所不同。
insert into table_name(column_list)
select select_list
from table_name
...;
3.例子
INSERT INTO bird_sightings
(bird_id, human_id, time_seen, location_gps)
SELECT birds.bird_id, humans.human_id,
date_spotted, gps_coordinates
FROM
(SELECT personal_name, family_name, science_name, date_spotted,
CONCAT(latitude, ';', longitude) AS gps_coordinates
FROM eastern_birders
JOIN eastern_birders_spottings USING(birder_id)
WHERE
(personal_name, family_name,
science_name, CONCAT(latitude, ';', longitude))
NOT IN
(SELECT name_first, name_last, scientific_name, location_gps
FROM humans
JOIN bird_sightings USING(human_id)
JOIN rookery.birds USING(bird_id))) AS derived_1
JOIN humans
ON(personal_name = name_first
AND family_name = name_last)
JOIN rookery.birds
ON(scientific_name = science_name);
INSERT INTO bird_sightings
(bird_id, human_id, time_seen, location_gps)
VALUES
(SELECT birds.bird_id, humans.human_id,
date_spotted, gps_coordinates
FROM
(SELECT personal_name, family_name, science_name, date_spotted,
CONCAT(latitude, ';', longitude) AS gps_coordinates
FROM eastern_birders
JOIN eastern_birders_spottings USING(birder_id)
WHERE
(personal_name, family_name,
science_name, CONCAT(latitude, ';', longitude))
NOT IN
(SELECT name_first, name_last, scientific_name, location_gps
FROM humans
JOIN bird_sightings USING(human_id)
JOIN rookery.birds USING(bird_id))) AS derived_1
JOIN humans
ON(personal_name = name_first
AND family_name = name_last)
JOIN rookery.birds
ON(scientific_name = science_name));
参考文献
MariaDB Insert Into Select
INSERT SELECT
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
