mysqlsqldatabase

The target table ... of DELETE is not updateable


I have such query

SET @n=0;
DELETE t3 FROM (
         SELECT  id, project_id, task_id, user_id,grouper
                            FROM (  
                                    SELECT  id, project_id, task_id, user_id,
                                    @n:=if(status=55,@n+1,@n),
                                    if(status=55,@n-1,@n) as grouper FROM timelog
                                    WHERE user_id='5' ORDER BY id ASC
                                 ) as t
                where grouper>-1
                group by grouper) as t3 WHERE grouper=1

for which i receive The target table t3 of the DELETE is not updatable

is there any solution for this error?
basically what i'm trying is to delete group of table rows marked with grouper using select in delete. i'm also happy for other solutions or ideas different than this one.

sql fiddle: http://sqlfiddle.com/#!2/33820/2/0

EDIT: thanks for the answers here is the working code(if anyone need something similiar):

SET @n=0;
delete from timelog where id in  ((SELECT  id
                    FROM (  
                            SELECT  id, project_id, task_id, user_id,
                            @n:=if(status=55,@n+1,@n),
                            if(status=55,@n-1,@n) as grouper FROM timelog
                            WHERE user_id='5' ORDER BY id ASC
                         ) as t
        where grouper>-1 and grouper=1
        group by grouper))

Solution

  • Wish I had more time...but fast psuedo code...

    delete from timelog where id in  ((SELECT  id
                            FROM (  
                                    SELECT  id, project_id, task_id, user_id,
                                    @n:=if(status=55,@n+1,@n),
                                    if(status=55,@n-1,@n) as grouper FROM timelog
                                    WHERE user_id='5' ORDER BY id ASC
                                 ) as t
                where grouper>-1
                group by grouper) as t3 WHERE grouper=1)
    

    all I'm doing is changing the subselect statement into a where clause that simply returns the ID's listed in your original subquery.

    edit - brackets are a bit off, I think I have it now. To be honest, this can really be cleaned up to one select statement, not the nested version here.