How can I insert rows in another table using this MySQL select statement?

Question

How can I use the results of this select statement to insert rows in another table?

SELECT "Old Town" AS neighborhoods, reportdatetime, IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((38.91911 -77.03648000000001,
38.918150000000004 -77.03979000000001,
38.917910000000006 -77.0403,
38.91734 -77.04073000000001,
38.91911 -77.03648000000001
 ))' )
)AS result, latitude, longitude, sport_type
FROM sport
WHERE IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((38.91911 -77.03648000000001,
38.918150000000004 -77.03979000000001,
38.917910000000006 -77.0403,
38.91734 -77.04073000000001,
38.91911 -77.03648000000001 ))' )
) = 1;

I'd like to put the results of this Select into a table called sports_by_neighborhood. Its structure is as follows:

neighborhoods (varchar 50)
result (tinyint 1)
reportdatetime (datetime)
latitude (decimal 11,9)
longitude (decimal 10,8)
sport_type (varchar 30)

dev.mysql.com/doc/refman/5.1/en/insert-select.html

Marc B
– Marc B

2011-08-26 16:41:40 +00:00
Commented Aug 26, 2011 at 16:41 — Marc B
– Marc B, Commented Aug 26, 2011 at 16:41

knittl · Accepted Answer · 2011-08-26 16:53:12Z

2

Simply use the insert select syntax:

INSERT INTO other_table ( neighborhoods, reportdatetime, result, latitude, longitude, sport_type )
SELECT "Old Town" AS neighborhoods, reportdatetime, IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((38.91911 -77.03648000000001,
38.918150000000004 -77.03979000000001,
38.917910000000006 -77.0403,
38.91734 -77.04073000000001,
38.91911 -77.03648000000001
 ))' )
)AS result, latitude, longitude, sport_type
FROM sport
WHERE IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((38.91911 -77.03648000000001,
38.918150000000004 -77.03979000000001,
38.917910000000006 -77.0403,
38.91734 -77.04073000000001,
38.91911 -77.03648000000001 ))' )
) = 1;

edited Aug 26, 2011 at 16:53

answered Aug 26, 2011 at 16:42

knittl

269k59 gold badges339 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sathish Kumar · Accepted Answer · 2011-08-26 16:53:32Z

0

INSERT INTO sports_by_neighborhood (neighborhoods, reportdatetime, result, latitude,longitude, sport_type)
 SELECT "Old Town" AS neighborhoods, reportdatetime, IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT(     'POLYGON((38.91911 -77.03648000000001,
38.918150000000004 -77.03979000000001,
38.917910000000006 -77.0403,
38.91734 -77.04073000000001,
38.91911 -77.03648000000001
 ))' )
)AS result, latitude, longitude, sport_type
FROM sport
WHERE IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT(     'POLYGON((38.91911 -77.03648000000001,
38.918150000000004 -77.03979000000001,
38.917910000000006 -77.0403,
38.91734 -77.04073000000001,
38.91911 -77.03648000000001 ))' )
) = 1;

edited Aug 26, 2011 at 16:53

answered Aug 26, 2011 at 16:49

Sathish Kumar

3691 gold badge3 silver badges10 bronze badges

Collectives™ on Stack Overflow

How can I insert rows in another table using this MySQL select statement?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related