MySQL: Insert Row If It Doesn't Already Exist?

Question

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;

possible duplicate of How to 'insert if not exists' in MySQL? — Francis Upton IV
– Francis Upton IV, Commented Dec 29, 2011 at 21:27

Community · Accepted Answer · 2017-05-23 12:33:46Z

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);

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Dec 29, 2011 at 21:25

Jack Edmonds

33.5k20 gold badges68 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

MySQL: Insert Row If It Doesn't Already Exist?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related