Mysql Nested sub queries unknown column error

Question

i have a pretty big query with 2 sub queries. My big query updates an entire column E. The table looks like this:

--------------------------
id  | A      | B    | E  |
--------------------------
1   |  NULL  | NULL |NULL|
--------------------------
2   |  4     | 6    |NULL|
--------------------------
3   |  6     | 9    |NULL|
--------------------------

This is my entire query below:

 Update mydatabase.mytable  ,
    (SELECT t1.A/AVG(t2.A)
    FROM    mytable t1
    JOIN    mytable t2
    ON      t2.id <= t1.id
    group by t1.id ) AS C,
    (SELECT t1.B/AVG(t2.B)
    FROM    mytable t1
    JOIN    mytable t2
    ON      t2.id <= t1.id
    group by t1.id ) AS D
    SET E = (C+D)/2;

I get the error : Unknown column 'C' in 'field list'

believe i will also get the same error for D.

My subqueries work and the syntax is correct. i am just unsure of the big query and the update part.

Thanks,

EDIT: How can i edit the ON clause part of the query above such that i would like the current code to work where id<=14 (which is t2.id <= t1.id as shown above) so when t1 id =14, t2 is all the cumulative id from 1 to 14 as it is now.

but for id >14 I would like the ON clause to be (t2.id=t1.id>=t1.id-2 and <=t1.id) so when t1 id=15, t2.id should be between 13 and 15. when t1 id =16, t2.id should be between 14 and 16 and so on.

This is because when i calculate col E for ids after id=14, i am only interested in getting the average of the previous 2 rows for C and D on a moving average.

M Khalid Junaid · Accepted Answer · 2014-05-21 07:36:59Z

2

You can rewrite your query using single join only no need to do additional join with conditions to calculate another value

Update t  join 
    (SELECT t1.A/AVG(t2.A) C ,t1.B/AVG(t2.B) D
    FROM    t t1
    JOIN    t t2
    ON      t2.id <= t1.id
    group by t1.id ) AS tt
    SET E = (tt.C + tt.D)/2;

Demo

Edit for null values

Update t  join 
    (SELECT t1.id ,ifnull(t1.A/AVG(t2.A),0) C ,ifnull(t1.B/AVG(t2.B),0) D
    FROM    t t1
    JOIN    t t2
    ON      t2.id <= t1.id
    group by t1.id ) AS tt
    on(t.id = tt.id)
    SET E = (tt.C + tt.D)/2;

Demo

edited May 21, 2014 at 7:36

answered May 21, 2014 at 7:18

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

jxn Over a year ago

hmm my col E is still all NULL

M Khalid Junaid Over a year ago

@jenn i have shown sample demo in my answer and it fills column e, can you also provide fiddle demo for the data set you are trying that would be easier for me

jxn Over a year ago

here is the fiddle link, sorry my first row is all NULL actually. sqlfiddle.com/#!2/33b7a/1

M Khalid Junaid Over a year ago

@jenn see my updated answer and updated demo

M Khalid Junaid Over a year ago

Yes you can leave it as null as here i guess this is what you are asking ?

|

Collectives™ on Stack Overflow

Mysql Nested sub queries unknown column error

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related