0

I have this query:

UPDATE tbl_writerremark SET status='no' WHERE id=(SELECT max(id) FROM tbl_writerremark WHERE wid=18)

and I am getting this error : 

 "you can't specify target table 'tbl_writerremark' for update in FROM clause"

Can anyone please help me to solve this query.

user2936213
  • 1,021
  • 1
  • 8
  • 19
user3256652
  • 1
  • 1

3 Answers3

0

Try this - 

UPDATE tbl_writerremark SET status='no' WHERE id=(select * from (SELECT max(id) FROM tbl_writerremark WHERE wid=18) as t)
Indra Yadav
  • 600
  • 5
  • 22
  • I have use this query but I get this error - "Every derived table must have its own alias" – user3256652 Jan 31 '14 at 09:24
  • Thaks for relpy for this, I have use this query and it's working. Query - UPDATE tbl_writerremark SET status='no' WHERE id=(select * from (SELECT max(id) FROM tbl_writerremark WHERE wid=18) as test) – user3256652 Jan 31 '14 at 09:28
0

This should work- 

UPDATE tbl_writerremark SET status='no' WHERE id=(SELECT max(id) FROM (select * from tbl_writerremark) as temp_ WHERE wid=18)

In a MySql update and insert, you can't reference the same table which you are modifying in the sub-query.

Kamehameha
  • 5,423
  • 1
  • 23
  • 28
0

In MySQL, you can't modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

You will need to stop using the nested subquery and execute the operation in two parts, or alternatively use a simple where clause.

You can use this query, but it's ugly for several reasons, including performance:

UPDATE tbl_writerremark SET status='no' WHERE id=(select * from (SELECT max(id) FROM tbl_writerremark WHERE wid=18) as temp_max)
MustDie1Bit
  • 560
  • 4
  • 16