Reputation: 1554
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
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
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