0

For some reason I got the error in MySql: You can't specify target table 'location' for update in FROM clause

UPDATE location 
SET 
    lat = 37.05,
    lng = - 122.05,
    power = 90,
    speed = 90,
    utc = '155',
    gmt = '156'
WHERE
    location.index IN (SELECT 
            MAX(location.index)
        FROM
            location
        WHERE
            location.truckid = '1480c667-0bc9-3c60-85f3-6de3b6cd5ad4');

What did I do wrong?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Duna
  • 1,564
  • 1
  • 16
  • 36

1 Answers1

1

Try to put another SELECT around the subquery.

UPDATE location 
       SET lat = 37.05,
           lng = -122.05,
           power = 90,
           speed = 90,
           utc = '155',
           gmt = '156'
       WHERE index = (SELECT index
                             FROM (SELECT max(index) index
                                          FROM location
                                          WHERE location.truckid = '1480c667-0bc9-3c60-85f3-6de3b6cd5ad4') x);
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • There is a canonical answer for this error here on SO that provides explanation as well, not just a solution. – Shadow May 15 '19 at 10:50
  • `UPDATE location SET lat = 37.1, lng = -122.05, power = 90, speed = 90, utc = '155', gmt = '156' WHERE location.index = (SELECT * FROM (SELECT max(location.index) FROM location WHERE location.truckid = '1480c667-0bc9-3c60-85f3-6de3b6cd5ad4') x);` the correct answer – Duna May 15 '19 at 12:29
  • 1
    @Duna: `SELECT *` without a `WHERE` in a scalar subquery certainly isn't the correct answer unless the table has only one column and a maximum of one row. At least the "only one" column condition can be ruled out as there are a more columns in the `SET` clause. – sticky bit May 15 '19 at 12:33