mysql "Unknown column in where clause"

Asked May 16 '18 at 12:54

Active May 16 '18 at 13:07

Viewed 31 times

I tried to write a procedure but when I try to run it I obtained this error:

Error Code: 1054. Unknown column 'lista1.ID' in 'where clause'

This is the routine code:

CREATE DEFINER=`root`@`%.zamberlan.local` PROCEDURE `TMTotal`()
BEGIN
    DROP temporary table if exists `lista1`;
    CREATE temporary TABLE `lista1` as
    (select 
        T.idarticolo as ID,
        avg(tempo) as TI
    from
        (select 
            idarticolo,
            ordineua,
            tempo
        From ordini 
        where tempo Is Not Null 
        group by ordineua 
        having count(ordineua) = 1) T
        group by T.idarticolo);
UPDATE
    articoli
set
    tempomediogg=lista1.TI 
where articoli.id=lista1.ID;
END

WHY?

asked May 16 '18 at 12:54

Massimo Griffani

What does your table structure look like? – Adam May 16 '18 at 13:03
1

Why? because the UPDATE statement doesnt use a reference to the temporary table lista1.. You need to INNER JOIN the lista1 within the UPDATE statement. – Raymond Nijland May 16 '18 at 13:04
Yes the INNER JOIN WAS MISSING! Thank you – Massimo Griffani May 16 '18 at 13:22

mysql "Unknown column in where clause"

0 Answers0