Nested subquery giving eror of unknown column I am facing uknown column 'id' issue which is exist in db_emp.test_etl. Then also it is giving uknown column error why?.This query successfully working in mysql version 8.0.14 but not mysql version 8.0.11.

18:30:48    
update db_emp.test_etl 
   set name='abc' 
 where id in( select max(SETNO) 
                from ( select sum(SETNO) 
                         from AUC1 
                        where SETNO=id
                     ) a
            )   
Error Code: 1054. Unknown column 'id' in 'where clause' 0.0024 sec



  db_emp.test_etl table structure :


CREATE TABLE `test_etl` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1



  Above query work for single level sub query as below :


12:02:57
update capitalmarket.test_etl 
   set name='abc' 
 where id in(select sum(SETTLEMENTNO) 
               from capitalmarket.AUCTION 
              where SETTLEMENTNO=id
            )
0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0  0.0020 sec
The problem here must be with the id reference in the innermost SELECT. For a long time MySQL has had trouble with resolving outer references in correlated subqueries if those references were placed inside derived tables in the correlated subqueries.
Apparently the problem has been resolved in 8.0.14:

Optimizer Notes

Previously, derived tables and common table expressions could not contain outer references. Outer references are now permitted.


If you want the query to compile both in 8.0.14 and in 8.0.11, apparently you need to rewrite it to somehow avoid referencing the id column in a derived table. Before making any suggestions, I must note that your example has other issues than the unresolved id reference. The very first line in this correlated subquery:
select max(SETNO)
from ( select sum(SETNO) 
       from AUC1 
       where SETNO=id
     ) a

already has a problem, because it references an unknown column SETNO. The derived table a does not have such a column.
So, for the sake of showing how to resolve the id reference issue, I am going to assume that sum(SETNO) was meant to be written as sum(SETNO) AS SETNO. That way at least* the outer level SETNO reference becomes valid.
Basically, the solution is to get the MAX(SETNO) in an alternative way, such that a derived table is actually not needed at all and thus the id column can be referenced directly in the correlated subquery. For that you will use ORDER BY and LIMIT 1. Specifically, you rewrite your correlated subquery as follows:
SELECT sum(SETNO)
FROM AUC1
WHERE SETNO = id
ORDER BY sum(SETNO) DESC
LIMIT 1

This way the correlated subquery is still getting the same value but no longer references outer columns from a derived table.

*The query still does not make much sense as it is, though. But at least it should compile now and allow us to demonstrate the solution.