Help with database query

Discussion in 'Databases' started by Asso, Apr 21, 2014.

0
  1. #1
    I am doind this query

    UPDATE trans t
    JOIN accounts ON(t.user_id = a.id)
    JOIN
    (SELECT user_id FROM trans GROUP BY user_id HAVING SUM(amount) > 0.00005460 ) tt
    ON(tt.user_id = a.id)
    GROUP BY a.id
    SET t.paidout=1

    and i get this error

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY a.id SET t.paidout=1' at line 6

    Any help would be apreciated
     
    Asso, Apr 21, 2014 IP
  2. Bharat Kumar Dhaker

    Bharat Kumar Dhaker Active Member

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    81
    #2
    Hi Asso, In your query you have syntax error. In UPDATE statement GROUP BY must be in sub-query. Thats why syntax error is occurred.
    Your final non error query is:

    UPDATE trans t
    JOIN accounts ON(t.user_id = a.id)
    JOIN
    (SELECT user_id FROM trans GROUP BY user_id HAVING SUM(amount) > 0.00005460 ) tt
    ON(tt.user_id = a.id)
    SET t.paidout=1
     
    Bharat Kumar Dhaker, Apr 23, 2014 IP
  3. Nishantrathore

    Nishantrathore Greenhorn

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    21
    #3
    A database is an organized collection of data. The data are typically organized to model relevant aspects of reality in a way that supports processes requiring this information.....
     
    Nishantrathore, May 15, 2014 IP
  4. Jigney

    Jigney Active Member

    Messages:
    168
    Likes Received:
    3
    Best Answers:
    1
    Trophy Points:
    93
    #4
    You can't use group by in the main jopin query. You have to use it in the subquery...
     
    Jigney, May 27, 2014 IP