如何在mysql中将一个表作为值插入另一个表？

[英]How can I insert one table as value to another one in mysql?

本文翻译自 ZHICHU ZHENG 查看原文 2016/03/09 25 database/ sql-insert/ mysql

I have these two tables, the first one is called item_coordinates, the types are INT, DOUBLE, DOUBLE

我有这两个表，第一个叫做item_coordinates，类型是INT，DOUBLE，DOUBLE

itemID       | latitude  |  longitude
-------------+-----------+-----------
1679323860   | 36.531398 |  -82.98085
1679340420   | 29.178171 | -74.075391
1679386982   | 40.73235  |   -94.6884

And now I have another table called Geocoordinates, I created this table as follow:

现在我有另一个名为Geocoordinates的表，我创建了这个表如下：

CREATE TABLE Geocoordinates (ItemID INT PRIMARY KEY,
 Geo_Coordinates POINT) ENGINE = MyISAM;

Now I want to insert the values in table 1 into table 2, but keep getting error messages. This is my attempt:

现在我想将表1中的值插入表2中，但不断收到错误消息。这是我的尝试：

INSERT INTO Geocoordinates (ItemID, Geo_Coordinates) VALUES (item_id, 
POINT(latitude, longitude)) SELECT item_id, latitude, longitude FROM 
item_coordinates);

Thanks in advance.

提前致谢。

2 个解决方案

#1

I think you need to use the CONCAT function to group the lat/long into one field

我认为您需要使用CONCAT函数将lat / long分组到一个字段中

INSERT INTO Geocoordinates (ItemID, Geo_Coordinates) VALUES (item_id, 
POINT(latitude, longitude)) SELECT item_id, CONCAT(latitude, longitude) FROM item_coordinates);

I don't think POINT is needed in this case.

在这种情况下我不认为需要POINT。

#2

Use INSERT INTO SELECT, no VALUES keyword should be specified. Below is the complete demo.

使用INSERT INTO SELECT，不应指定VALUES关键字。以下是完整的演示。

SQL:

SQL：

-- Data
create table item_coordinates( itemID bigint,  latitude decimal(10,6), longitude decimal(10,6));
CREATE TABLE Geocoordinates (ItemID INT PRIMARY KEY,
 Geo_Coordinates POINT) ENGINE = MyISAM;
INSERT INTO item_coordinates values
(1679323860   , 36.531398 ,  -82.98085),
(1679340420   , 29.178171 , -74.075391),
(1679386982   , 40.73235  ,   -94.6884);
SELECT * FROM item_coordinates;

-- SQL needed
INSERT INTO Geocoordinates (itemID, Geo_Coordinates)  SELECT itemID, POINT(latitude, longitude)  FROM item_coordinates;
SELECT itemID, ST_AsText(Geo_Coordinates) FROM Geocoordinates;

Output:

输出：

mysql> SELECT * FROM item_coordinates;
+------------+-----------+------------+
| itemID     | latitude  | longitude  |
+------------+-----------+------------+
| 1679323860 | 36.531398 | -82.980850 |
| 1679340420 | 29.178171 | -74.075391 |
| 1679386982 | 40.732350 | -94.688400 |
+------------+-----------+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Geocoordinates (itemID, Geo_Coordinates)  SELECT itemID, POINT(latitude, longitude)  FROM item_coordinates;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT itemID, ST_AsText(Geo_Coordinates) FROM Geocoordinates;
+------------+-----------------------------+
| itemID     | ST_AsText(Geo_Coordinates)  |
+------------+-----------------------------+
| 1679323860 | POINT(36.531398 -82.98085)  |
| 1679340420 | POINT(29.178171 -74.075391) |
| 1679386982 | POINT(40.73235 -94.6884)    |
+------------+-----------------------------+
3 rows in set (0.00 sec)

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2016/03/09/fd4dcca97bc6be320e23715ae667b0d8.html。

猜您在找

如何从一个mysql表INSERT INTO到另一个表并设置一列的值？ - How do I INSERT INTO from one mysql table into another table and set the value of one column? 如何使用自动递增的ID将多行插入一个表，然后插入另一个表？ MySQL的 - How can I insert multiple rows into one table, then into another table using an auto incremented ID? mysql 如何将一个mySQL表的值连接到另一个表的值？ - How can I connect a value of one mySQL table to a value of another table? 如何查找表中的值并将其插入到另一个表中? - How do I lookup a value in a table and insert it into another table? 使用MySQL，如何在另一个表中不存在该值的情况下插入表中？ - With MySQL, how do I insert into a table on condition that the value does not exist in another table?

赞助商链接