CODEWITHSUNDEEP
mysql
tatty27
tatty27

Reputation: 1554

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

Upvotes: 0

Views: 259

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133410

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
  )

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

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
  )

Upvotes: 1

Related Questions