tatty27 tatty27 - 5 months ago 26
MySQL Question

mysql Every derived table must have its own alias for DELETE query

I have found an answer but it refers to SELECT rather than DELETE and I am not sure how to implement the given answer in this context.

With the query below I want to delete all but the last 10 most recent values (defined by id_lv) but only for one user.

DELETE FROM last_viewed
WHERE id_lv <= (
SELECT id_lv
FROM (
SELECT id_lv
FROM last_viewed
WHERE user_id_lv = '$user_id'
ORDER BY id_lv DESC
LIMIT 1 OFFSET 10
)
)


When I run it i get
Every derived table must have its own alias

Answer

You have to use an alias in the derived table used by the subquery of the DELETE statement:

DELETE FROM last_viewed
  WHERE id_lv <= (
    SELECT id_lv
    FROM (
      SELECT id_lv
      FROM last_viewed
      WHERE user_id_lv = '$user_id'
      ORDER BY id_lv DESC
      LIMIT 1 OFFSET 10
    ) AS t -- You need an alias here
  )
Comments