0

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

tatty27
  • 1,553
  • 4
  • 34
  • 73
  • 1
    Possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) – Matt Raines Jun 12 '16 at 16:29
  • @MattRaines I mentioned that I have found a similair question but I was unsure how to apply it to a delete rather than a select – tatty27 Jun 12 '16 at 21:28

2 Answers2

1

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
  )
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
1

Use in and alias for table (FROM)

DELETE FROM last_viewed
  WHERE id_lv in  (
    SELECT  t.id_lv
    FROM (
      SELECT id_lv
      FROM last_viewed
      WHERE user_id_lv = '$user_id'
      ORDER BY id_lv DESC
      LIMIT  10
    ) as t
  )
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107