3

How do I change my MySQL statement so that it only inserts a row into table_b if the property's id isn't already in table_b?

INSERT INTO table_b( property_id, siteaddress, area_name, result) 
SELECT property_id, siteaddress,   "Area A" AS area_name,  IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((44.933690000000006, -111.07178
44.96479, -104.1504
41.062780000000004, -104.04053
41.01306, -111.07178
44.887010000000004, -111.04981000000001
))' )
)AS result
FROM table_a
WHERE IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((44.933690000000006, -111.07178
44.96479, -104.1504
41.062780000000004, -104.04053
41.01306, -111.07178
44.887010000000004, -111.04981000000001 ))' )
) = 1;

INSERT INTO table_b( property_id, siteaddress, area_name, result) 
SELECT property_id, siteaddress,   "Area B" AS area_name,  IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((37.909530000000004, -87.69288
37.89219000000001, -82.5293
36.40359, -83.58399
35.78217, -86.33057000000001
37.90872, -87.69356
37.909530000000004, -87.69288
))' )
)AS result
FROM table_a
WHERE IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((37.909530000000004, -87.69288
37.89219000000001, -82.5293
36.40359, -83.58399
35.78217, -86.33057000000001
37.90872, -87.69356
37.909530000000004, -87.69288 ))' )
) = 1;
Laxmidi
  • 2,580
  • 11
  • 45
  • 78
  • 1
    possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) – Francis Upton IV Dec 29 '11 at 21:27

1 Answers1

9

You can use MySQL's INSERT IGNORE syntax as in this question: How to 'insert if not exists' in MySQL?

For example:

INSERT IGNORE INTO tbl1 (id, col) VALUES (1, 2);
Community
  • 1
  • 1
Jack Edmonds
  • 28,069
  • 16
  • 60
  • 77