Updating the last row MYSQL

Question

I want to update the last row in a mysql table

UPDATE logend 
   SET endsecs = endsecs+'$moretime' 
 WHERE id = (SELECT id FROM logend ORDER BY id DESC LIMIT 1)

But it doesn't work, because of this error:

ERROR 1093 (HY000): You can't specify target table 'logend' for update in FROM clause

Set in SQL syntax "SELECT max(id) FROM logend" if the latest record has the last id — liontass, Jan 01 '21 at 21:55
"but it doesn't work." ?? What do you mean by that? Should we really guess why that does not work? You probably are getting an error? Why did you not add this info to your question? — Luuk, Jan 01 '21 at 21:55
@luuk i guess everybody else understood it but you, anyway it is solved, thanks — Mido H, Jan 01 '21 at 22:21
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jan 02 '21 at 00:40
@MidoH: The question should have been closed anyway, because the answer has already been given. see: https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Luuk, Jan 02 '21 at 09:38

score 1 · Accepted Answer · answered Jan 01 '21 at 21:58

In MySql you can't use the updated table in a subquery the way you do it.
You would get the error:

You can't specify target table 'logend' for update in FROM clause

What you can do is an UPDATE with ORDER BY and LIMIT:

$sql = "UPDATE logend SET endsecs=endsecs+'$moretime' ORDER BY id DESC LIMIT 1";

Dostrelith · Answer 2 · 2021-01-01T22:26:07.697

1

Can't you just get the max(id) and update that?

$sql = "UPDATE logend SET endsecs=endsecs+'$moretime' WHERE id = (
    SELECT id FROM (
    SELECT MAX(id) FROM logend) AS id
     )";

edited Jan 01 '21 at 22:26

answered Jan 01 '21 at 22:05

Dostrelith

922
5
13

score 0 · Answer 3 · answered Jan 01 '21 at 22:09

Here's another solution: a self-join, to find the row for which no other row has a greater id.

Also, you should really not interpolate POST inputs directly into your SQL statement, because that exposes you to SQL injection problems. Use a query parameter instead.

$moretime = $_POST['moretime'];

$sql = "
  UPDATE logend AS l1
  LEFT OUTER JOIN logend AS l2 ON l1.id < l2.id
  SET l1.endsecs = l1.endsecs + ? 
  WHERE l2.id IS NULL";
$stmt = $mysqli->prepare($sql);
if (!$stmt) {
  trigger_error($mysqli->error);
  die($mysqli->error);
}
$stmt->bind_param("s", $moretime);
$ok = $stmt->execute();
if (!$ok) {
  trigger_error($stmt->error);
  die($stmt->error);
}

Updating the last row MYSQL

3 Answers3