1093 error in mysql that can't specify target table for update in from clause

Question

I have a table in database where I am trying to update some data.

Table1

Uname   Password   Email          SpecialID
 A      qwh2       abc@xyz.com    23243
 B      rt4f       aafj@xyz.com   56343

I am trying to change the SpecialID from this query:

Update Table1 SET SpecialID='24152' where Uname=(select Uname from Table1 where Email='abc@xyz.com');

But I am getting this error :

ERROR 1093 (HY000): You can't specify target table 'Table1' for update in FROM clause

Please help me and tell me what is wrong I am doing in this query...!!! I searched but didn't get proper solution..

You are trying to update or delete from a table and querying the same table. Please see this question: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Ozair Kafray, May 26 '14 at 08:01

score 2 · Answer 1 · answered May 26 '14 at 07:59

2

Use join instead,in mysql you can't use same table in update/delete in sub queries.

Update Table1 t
join Table1  t1 on(t.Uname = t1.Uname)
SET SpecialID='24152' 
where t1.Email='abc@xyz.com'

answered May 26 '14 at 07:59

M Khalid Junaid

score 1 · Answer 2 · answered May 26 '14 at 08:01

1

Yo only need to change the condition:

Update Table1 SET SpecialID='24152' where Email='abc@xyz.com';

I hope it works fine for you.

answered May 26 '14 at 08:01

fmgonzalez

This will work I know.. But this is just as example. I want to use this in my application where your solution won't work... – user3660303 May 26 '14 at 08:04
@user3660303 then you should ask about what you're _actually_ doing. It's not useful to ask a specific question and then say "ah but actually I'm not doing what I asked, I'm doing something else, and in something-else that won't work". – AD7six May 26 '14 at 08:04

2 Answers2