MySql : Updating all except the first row

Question

I went through the previous answer like this, but it gives me the following error : You can't specify target table 'table_name' for update in FROM clause.

I have a table with say 3 columns (id -> auto increment primary id) :

id, roll_no and attendance

And for selected roll numbers having many entries each, except the first entry I want to update all entry attendance field as P.

The query which I wrote is following :

UPDATE tbl_class_attendance 
   set attendance = 'P' 
 where id NOT IN 
   (Select min(id) 
      from tbl_class_attendance 
     WHERE roll_no IN ('25', '45', '55') 
     GROUP 
       BY roll_no;

But it gives me the above error. I also went through other answers asking to use two select queries but there the answer I didn't find completely easy to understand as well as difficulty in executing for my selected list of roll numbers.

So, is there a way to update?

EDIT : Answer given below

See this [thread](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Sergiu, Oct 30 '20 at 08:36
@Sergiu The answer doesn't work with Group by in nested select — arqam, Oct 30 '20 at 08:37
Use multiple-table UPDATE syntax - the subquery must be in UPDATE clause tables list, not in WHERE clause. — Akina, Oct 30 '20 at 08:40
@Strawberry It is giving the error `You can't specify target table 'table_name' for update in FROM clause` — arqam, Oct 30 '20 at 08:42
@Akina But my inner query is giving the result for my Where clause — arqam, Oct 30 '20 at 08:42
You cannot use the subquery in WHERE when it uses the table to be updated. — Akina, Oct 30 '20 at 08:43

score 0 · Answer 1 · answered Oct 30 '20 at 08:44

UPDATE tbl_class_attendance t1
LEFT JOIN ( SELECT t2.roll_no, MIN(t2.id) id
            FROM tbl_class_attendance t2
            WHERE t2.roll_no IN ('25', '45', '55') 
            GROUP BY t2.roll_no ) t3 USING (roll_no, id)
SET t1.attendance = 'P' 
WHERE t3.id IS NULL;

arqam · Accepted Answer · 2020-10-30T09:12:01.953

0

Got it working by following :

Update table SET a=value WHERE x IN
          (Select * from (select x from table where condition) as t)

Credit : https://stackoverflow.com/a/43610081/6366458

edited Oct 30 '20 at 09:12

answered Oct 30 '20 at 08:50

arqam

3,582
5
34
69

MySql : Updating all except the first row

2 Answers2