0

I am trying to create a table that I can use to compare a set 8 of GPS co-ordinates. Eventually I want to check that these co-ordinates are no more than 20m apart. I am currently having trouble populating this table as I keep getting the following error:

Error Code: 1093. You can't specify target table 'GPS1' for update in FROM clause

I have tried changing my query a few times, with no luck.

Currently this is what I have:

UPDATE ots_outlet_gps AS GPS1
LEFT JOIN

(SELECT *
FROM

    (SELECT    
        TMP.store_code
    ,   TMP.gps
    ,   TMP.action_date
    FROM tmp_outlet_gps TMP
    JOIN
    (SELECT * 
    FROM 
        ots_outlet_gps JOI
    WHERE 
        action_date1 > (SELECT action_date1 FROM ots_outlet_gps AS AA WHERE store_code = JOI.store_code GROUP BY store_code) 
    ) INN
    ON 
    TMP.store_code = INN.store_code
    WHERE 
        action_date >= '2019-01-01'
    AND action_date <= '2019-01-06'   
    ) PRNK
) SRC
ON
    GPS1.store_code = SRC.store_code  
SET
    GPS1.gps2 = SRC.gps 
,   GPS1.action_date2 = SRC.action_date  
WHERE 
    GPS1.gps2 IS NULL
AND GPS1.action_date2 IS NULL
;

TABLE STRUCTURE (ots_outlet_gps):

id              int(6)
store_code      bigint(12)
action_date1    date
gps1            varchar(20)
variance1       decimal(8,2)
action_date2    date
gps2            varchar(20)
variance2       decimal(8,2)
etc

TABLE STRUCTURE (tmp_outlet_gps):

store_code  int(10)
gps         varchar(20)
action_date date

Any help would be appreciated. I'm also not sure if I am using the correct approach for the desired end result, and would also be open to alternative suggestions.

Thanks.

Leon Claassen
  • 183
  • 3
  • 12
  • You should not be seeing this error from the above query AFAIK. You might want to post a more minimal question, as without a demo it could be difficult for someone to help you here. – Tim Biegeleisen Mar 08 '19 at 05:43
  • the error message seems not related to your query .. check better .. – ScaisEdge Mar 08 '19 at 06:51
  • Try changing `FROM ots_outlet_gps JOI` to `FROM (SELECT * FROM ots_outlet_gps) JOI`. See https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/4429409 – Nick Mar 08 '19 at 07:23
  • Possible duplicate of [You can't specify target table for update in FROM clause](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) – Nick Mar 08 '19 at 07:24
  • Incidentally, the figures in parentheses after the INT/BIGINT keywords are almost completely meaningless. – Strawberry Mar 08 '19 at 08:28
  • @Nick, thanks for the suggestion. I did try your suggestion, but the error message remains the same. – Leon Claassen Mar 08 '19 at 08:57

0 Answers0