MySQL – Update all but N items

[XaviJr]

I have a database growing bigger and bigger and I just want a query that I can call regularly and set all but the most recent N items to an arquived status.

I did some research and I found this (in this example N is 200):

[CODE]
UPDATE entries
SET arquived = 1
WHERE id NOT IN (
SELECT *
FROM entries
ORDER BY added DESC
LIMIT 200
) AS oldies
[/CODE]

The problem is that phpmyadmin is giving this error:
#1235 – This version of MariaDB doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

I also played a little with inner join but couldn’t do it.

A little help please? Thank you.

[XaviJr]

Quick update..

I accomplished to make the select with left join:

[CODE]
SELECT *
FROM entries t1
LEFT JOIN (
SELECT *
FROM entries
ORDER BY added DESC
LIMIT 200
) AS t2
ON t1.id = t2.id
WHERE t2.id IS NULL
[/CODE]

But the update is getting me an error:

[CODE]
UPDATE t1
SET arquived = 1
FROM entries t1
LEFT JOIN (
SELECT *
FROM entries
ORDER BY added DESC
LIMIT 200
) AS t2
ON t1.id = t2.id
WHERE t2.id IS NULL
[/CODE]

[XaviJr]

Solved.

[Author]

Posts