0

I have two tables, a "data" table with hundreds of thousands of rows, and a "settings" table with hundreds of rows. I need to update two values in the settings table, based on the sum of one column in the data table being greater than another column in the settings table, where the 'id' of the settings table matches the 'offerid' of the data table.

I've been cruising Google looking at many possible solutions, but I don't seem able to bend any of them to my needs. Any time I start working with Joins, I inevitably get tripped up.

My latest attempt which did not work, is:

UPDATE settings a, data b
SET a.hidden=1, a.weight=0
WHERE a.id = b.offerid
AND sum(b.views) > a.target

I liked this flavor of approach as I thought it actually made sense to me, but all I get is "Error code 1111: Invalid us of group function". Maybe someone here can point me in the right direction.

JVC
  • 793
  • 3
  • 8
  • 21

2 Answers2

1

It errors out because you are using an aggregate function (sum) with no grouping. So query really does not know which rows should be summed together. You can use a subquery like this:

UPDATE settings a
    INNER JOIN
    (
      SELECT
        sum(b.views) sumviews, b.offerid
        from data b
        group by b.offerid
    ) c on c.offerid = a.id 
SET a.hidden=1, a.weight=0
where c.sumviews>a.target

EDIT: To disable the safe update function do the following steps

Follow the steps below before executing the UPDATE command:

Go to Edit --> Preferences
Click "SQL Queries" tab and uncheck "Safe Updates" check box
Query --> Reconnect to Server
Now execute your sql query

Source: Disable Safe Update

Community
  • 1
  • 1
TheProvost
  • 1,832
  • 2
  • 16
  • 41
  • Thanks for that, but I just get a syntax error with the `where`. Also should it be `sum(b.views) AS sumviews`? Seems like an AS is missing to me. – JVC Nov 06 '14 at 00:47
  • AS is optional. You can add it if you want to. It is actually more readable with AS. May i know what error you are having? – TheProvost Nov 06 '14 at 00:53
  • Oh got it now. got the set and where interchanged. Updated my answer and i bet its already ok. Don't forget to upvote and marked this as answer if this has helped you :) – TheProvost Nov 06 '14 at 00:59
  • Hmmm... MySQL Workbench says: `Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column`. But there is clearly a where clause in the code, so I wonder what is it complaining about? – JVC Nov 06 '14 at 01:02
  • Oh its because you are using safe update mode on. It means that a primary key should be in your where clause but that is not what we want. Let me update my answer for your. – TheProvost Nov 06 '14 at 01:06
  • Ahh I see, it wasn't that there was no where clause, it was that the KEY was not in that clause. Got it! Thanks so much, this works perfectly. :) – JVC Nov 06 '14 at 03:08
  • Glad to know i helped. Have fun coding :) – TheProvost Nov 06 '14 at 03:12
-1

Try using 

HAVING

instead of 

AND

So your code goes like:

    UPDATE settings a, data b
    SET a.hidden=1, a.weight=0
    WHERE a.id = b.offerid
    GROUP BY a.id
    HAVING sum(b.views) > a.target
Vincent
  • 131
  • 7
  • Very interesting, thanks for that! Unfortunately now I just get a Syntax error from `having`. – JVC Nov 06 '14 at 00:44
  • You cant use "HAVING" without a group by clause. Downvoted this but can cancel if you edit your answer :) – TheProvost Nov 06 '14 at 01:01
  • Thanks for pointing out the GROUP BY. Unfortunately, I still get the same basic syntax error. – JVC Nov 06 '14 at 01:06
  • Hmmm its probably because you need to group by the b.offerid as well since it is not part of the aggregated function. But not really sure. I tend to use subqueries for this type of problems as you can see in my answer above. – TheProvost Nov 06 '14 at 01:12