1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help with database query

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

  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
    SEMrush
    Any help would be apreciated
     
    Asso, Apr 21, 2014 IP
    SEMrush
  2. Bharat Kumar Dhaker

    Bharat Kumar Dhaker Member

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #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