#1093 - You can't specify target table 'tbl' for update in FROM clause

Question

Unable to execute below query

Update tbl SET alias_seq= (Select seq from tbl where analysed_object =
'data' order by seq LIMIT 1)

getting below error

#1093 - You can't specify target table 'tbl' for update in FROM clause

John Joe · Accepted Answer · 2017-12-22T08:27:20.267

4

You cannot update the rows from the same data source which your sub query refers to.

Modify your query to this

UPDATE tbl SET alias_seq= (SELECT * FROM (Select seq from tbl where analysed_object = 'data' order by seq LIMIT 1) as X)

Here nested sub query makes a temporary table. So it doesn’t count as the same table you’re trying to update data from. In other words in MySQL, you can’t modify the same table which you use in the SELECT part.

You may refer here for more information UPDATE Syntax.

edited Dec 22 '17 at 08:27

answered Dec 22 '17 at 08:13

John Joe

12,412
16
70
135

Thanks, it works for me – VVB Dec 22 '17 at 09:37
But it is updating same id to all records – VVB Dec 22 '17 at 09:46
how was it now ? – John Joe Dec 22 '17 at 15:20
1

Problem solved, needed 1 more where Clause for outer query – VVB Dec 24 '17 at 03:02

#1093 - You can't specify target table 'tbl' for update in FROM clause

1 Answers1