CODEWITHSUNDEEP
sqldatabasemariadb
Manish Verma
Manish Verma

Reputation: 21

#1093 Table 'table' is specified twice, both as a target for 'DELETE' and as a separate source for data

not getting desire result in mysql query

i have searched a lot but didn't find solution

DELETE FROM table1 WHERE username NOT IN (select t1.id from table1 as t1
inner join table1 as t2 on t1.username = t2.username and t1.id <= t2.id 
group by t1.username , t1.id having count(*) <= 5 order by t1.username , t1.id desc);

Output is as follows:-

query should delete all rows except last 5 rows of each username

Upvotes: 1

Views: 842

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You seem to want to keep the most recent five ids for each user name. I think the simplest method uses window functions:

delete t1
    from table1 t1 join
         (select t1.*, row_number() over (partition by username order by id desc) as seqnum
          from table1 t1
         ) tt1
         on t1.username = tt1.username and
            t1.id = tt1.id
    where tt1.seqnum > 5;

Upvotes: 0

Y.K.
Y.K.

Reputation: 692

should work for you

delete from
    table1
where
    id in ( select
                id
            from
                (   select
                        *,
                        row_number() over(  partition by
                                                username
                                            order by
                                                id desc) as rn
                    from 
                        table1)
            where
                rn > 5)

Upvotes: 1

Related Questions