0

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

closed as unclear what you're asking by ypercubeᵀᴹ, mustaccio, Colin 't Hart, Vérace, Marco May 5 at 18:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Do you have id in the table AUC1? Can you post your AUC1 table as well? – temo May 2 at 7:21
  • You do know that you have SETNO in your first subquery and SETTLEMENTNO in the second? And show us the output of SHOW CREATE TABLE test_etl;. – Vérace May 2 at 9:21
  • 1
    It is unclear what the UPDATE is supposed to do. Please explain so we can help you rewrite it. Nested subquueries in MySQL have limitations, so you'll need to remove the double nesting.. – ypercubeᵀᴹ May 4 at 11:21
1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.