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))
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.