CODEWITHSUNDEEP
mysqlsql
Ranjit Kumar
Ranjit Kumar

Reputation: 781

sql error(1242) : sub query returns more than 1 row

i have two tables a_daily and o_daily_lcsgeneration:

i am trying this query : 

update a_daily
set
  a_daily.Turbine_Generation =  
   (
     select sum(o_daily_lcsgeneration.Turbine_Generation) 
     from o_daily_lcsgeneration 
     where o_daily_lcsgeneration.Location = 1 
     group by o_daily_lcsgeneration.Date
  )

but received the above error saying sub query more than 1 row

Upvotes: 0

Views: 555

Answers (3)

John Woo
John Woo

Reputation: 263723

remove the GROUP BY clause

UPDATE a_daily
SET a_daily.Turbine_Generation = 
(
        SELECT sum(o_daily_lcsgeneration.Turbine_Generation)
        FROM o_daily_lcsgeneration
        WHERE o_daily_lcsgeneration.Location = 1
)

it causes the query to return more than one value of you have multiple dates.

UPDATE 1

UPDATE  a_daily a
        INNER JOIN
        (
            select g.Date, sum(g.Turbine_Generation) totalSum
            from o_daily_lcsgeneration g
            where g.Location = 1 
            group by g.Date
        ) b ON a.date = b.date
SET     a.Turbine_Generation = b.totalSum
WHERE       a.location = 1

or

UPDATE  a_daily a
        LEFT JOIN JOIN
        (
            select g.Date, sum(g.Turbine_Generation) totalSum
            from o_daily_lcsgeneration g
            where g.Location = 1 
            group by g.Date
        ) b ON a.date = b.date
SET     a.Turbine_Generation = COALESCE(b.totalSum, 0)

Upvotes: 3

Vikram Jain
Vikram Jain

Reputation: 5588

update a_daily
set
  a_daily.Turbine_Generation =  (
     select sum(o_daily_lcsgeneration.Turbine_Generation) 
     from o_daily_lcsgeneration where o_daily_lcsgeneration.Location = a_daily.Location  
     group by o_daily_lcsgeneration.Date
  )

Upvotes: 1

GregD
GregD

Reputation: 2867

Run the subquery and you'll see that it returns more than one row. You are grouping the results by date, so the SUM expression calculates the value for every day. You could re-write the query as follow:

UPDATE a_daily
SET a_daily.Turbine_Generation = (SELECT SUM(o_daily_lcsgeneration.Turbine_Generation) 
                                    FROM o_daily_lcsgeneration 
                                   WHERE o_daily_lcsgeneration.Location = 1 
                                     AND o_daily_lcsgeneration.Date = 'YYYY-MM-DD'
                                GROUP BY o_daily_lcsgeneration.Date)

or (for a total sum of all events):

UPDATE a_daily
SET a_daily.Turbine_Generation = (SELECT SUM(o_daily_lcsgeneration.Turbine_Generation) 
                                    FROM o_daily_lcsgeneration 
                                   WHERE o_daily_lcsgeneration.Location = 1)

Upvotes: 1

Related Questions