SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Zealot Hieronymus's Avatar
    Join Date
    Nov 2004
    Location
    Nederland, Eindhoven
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Convert INSERT INTO to an UPDATE

    hi,

    I've a query that matches GPS points to locations. This is done periodically for every new point. So points that are not in a location get the value 'NULL' as location_id.
    Now I want an update function for if I change or add existing locations. First off, lets just assume we only add locations (by adding them or making existing ones bigger)

    I have this inser query:
    Code:
    INSERT INTO gps_point_locations (gps_point_id, location_id) 
    SELECT GpsPoint.id AS gps_point_id, Location.id AS location_id 
    FROM gps_points AS GpsPoint
    INNER JOIN gps_points_geo AS gp ON GpsPoint.id = gp.gps_point_id 
    LEFT JOIN locations AS Location ON CONTAINS(Location.geo_poly, gp.gps_point_geo) 
    WHERE GpsPoint.date BETWEEN "2012-04-27 00:00:00" AND "2012-04-27 23:59:59"  AND GpsPoint.unit_id=91
    One table has the gps points (gps_points)
    one table has for each gps_point a geo-point (gps_point_geo)
    one table has all the locations (locations)
    one table has the result with points matched to a location (gps_point_locations)

    As you can see I changed the query to select not only new points, but just points that fall between a period of a track (in which the vehicle passed through the changed locations)
    This query will be very heavy if not set up properly.
    Any ideas how to make a UPDATE function for this?
    What good are one-liners if they don't
    fit.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •