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.

group by, insert/update, where

Discussion in 'MySQL' started by Sycrid, Jul 16, 2015.

  1. #1
    Hi everyone,

    I'm trying to figure out a query here and I'm a little bit lost as to where I'd be placing the Where query in my attempt.

    What I'm trying to achieve is to query one table where all network_points are grouped by uid_owner and then moved to the uid_table (current_points) where uid_owner equals the uid

    INSERT INTO uid_table (current_points)
    SELECT sum(network_points)
    FROM nid_table
    GROUP BY uid_owner";
    Code (markup):
    What the above does, it totals based on uid_owner and then moves to the uid_table, but inserts these as new records with the totals. What I'm missing is the where statement or another comparative option.

    Can anyone assist with this?
     
    Solved! View solution.
    Sycrid, Jul 16, 2015 IP
  2. #2
    Are you just trying to update the value in the uid_table?

    You would want to use an update statement instead of an insert statement, if I am understanding the problem correctly.

    Something like this (Untested):

    
    UPDATE uid_table uid
    INNER JOIN
    (SELECT uid_owner,
    Sum(network_points) AS total_points
    FROM nid_table
    GROUP BY uid_owner) nid ON uid.uid = nid.uid_owner
    SET uid.current_points = nid.total_points
    
    Code (markup):
     
    jestep, Jul 17, 2015 IP
  3. Sycrid

    Sycrid Well-Known Member

    Messages:
    130
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    128
    #3
    This was the perfect answer. I'm not familiar enough to fully understand joins. Fair comment as well on using update instead of insert. Thanks for the help though Jestep, as I've been able to mod this a bit further to get some other things performed in a cron job on the system I'm setting up.

    Thanks agian.

     
    Sycrid, Jul 19, 2015 IP