I'm trying to write a query that will delete a users oldest rows (record of viewed car) if they have more than 10 rows in my table.
I was using rownum to collect the items from 1-10 (which will be excluded from delete)...however rownum has caused an error (shown below).
Can anyone help me resolve this error or come up with a different approach?
DELETE FROM `my_table` WHERE `car_id` NOT IN
( SELECT alias_table.`car_id` FROM
( SELECT `car_id` FROM `my_table` ORDER BY `id` DESC ) AS alias_table
WHERE rownum < 10 AND user_id = 0
);
Error: Unknown column 'rownum' in 'where clause'
The problem is your rownum is not in any select list. Is it from the table or is it a calculated row ? Fixing your query depends on answer for that question.
If you want to delete all the records for a user other than latest ten records, you can use something like below.
DELETE FROM my_car WHERE id NOT IN (SELECT temp.id FROM (SELECT id FROM my_car mc WHERE mc.user_id = 1 ORDER BY mc.id DESC LIMIT 10) AS temp) AND user_id = 1;