Update with not in in clause in mysql

Question

From my perspective this query should work

UPDATE tab_antiguedad set rol_id = 4 
where ant_id = 29 
      and rol_id not in (
                         select rol_id 
                         from tab_antiguedad 
                         where usu_id = 55
                        )

Where (select rol_id from tab_antiguedad where usu_id = 55) here rol_id has 4 and 3.

But the mistake that throws me is:

You can't specify target table 'tab_antiguedad' for update in FROM clause

Please help.

score 0 · Answer 1 · answered Jun 02 '16 at 13:43

0

The thing is that you cannot update the same table you used in the select part.

UPDATE tab_antiguedad

set rol_id = 4

where ant_id = 29 and

rol_id not in (select rol_id from tab_antiguedad where usu_id = 55)

answered Jun 02 '16 at 13:43

Pic

139
1
8

So what would be the solution? – Wilmer Hilaquita Jun 02 '16 at 14:18
I do not know much about subqueries, since I mainly use simple SQL commands, but I found a similiar question which I think that can help you. Just click [THIS](http://stackoverflow.com/questions/13283940/select-query-in-where-clause-of-update-query?lq=1) to open it. – Pic Jun 02 '16 at 14:53

ScaisEdge · Answer 2 · 2016-06-09T15:19:54.867

0

Try using alias

UPDATE tab_antiguedad as t1, (select rol_id 
        from tab_antiguedad   where usu_id = 55) as t2
set t1.rol_id = 4 
where t1.ant_id = 29 
and t1.rol_id != t2.rol_id

edited Jun 09 '16 at 15:19

answered Jun 02 '16 at 13:49

ScaisEdge

131,976
10
91
107

Gives the following error: You can't specify target table 't1' for update in FROM clause – Wilmer Hilaquita Jun 02 '16 at 14:20
Thank ScaisEdge, but strangely "not in" should not allow update if t1.rol_id is 3 or 4, by allowing the update? – Wilmer Hilaquita Jun 02 '16 at 15:48
Ok, I need you in the "not in" compare with all rol_id if one exists should not upgrade anything. – Wilmer Hilaquita Jun 02 '16 at 15:56
Could be not exists ... i have update the answer .. otherwise update you questiion with a proper, clear sample .. – ScaisEdge Jun 02 '16 at 15:59
The problem is that t2.rol_id only shows "4" when it should show "4 and 3" – Wilmer Hilaquita Jun 02 '16 at 16:11
Ok then should be != t2.rol_id ... i have update the answer – ScaisEdge Jun 02 '16 at 16:22
the process does not work because (select id from tab_antiguedad Where t2.rol t2.usu_id = 55) only issues you 1 record, and what I need is to send me the number of records there – Wilmer Hilaquita Jun 09 '16 at 14:16
Show a proper sample of data .. please because I can not understand – ScaisEdge Jun 09 '16 at 15:21

Update with not in in clause in mysql

2 Answers2