Update total in PHP/MYSQL Update

NZ_Kiwis

I'm looping through results from one table and want to update another table each time.

For example I have a Users table and with UserID and UserPoints. I'm looping through a Feedback table and am getting all the points scored on a particular user.

What I would deem to be bad coding is for each feedback row selecting the user from the users table, getting the existing points, adding the feedback points to it and then updating the row.

Is there a way I can do something like this?

UPDATE Users SET UserPoints = (UserPoints + $FeedbackPoints) WHERE UserID = $ID

I know I could do a count join search but there are fields here which i why i want this set up like below.

Weedpacket

Thing to remember is that the result of a SELECT statement is just as much a relation as one created by "CREATE TABLE".

I don't know about MySQL (consult the documentation), but in PostgreSQL one can write

UPDATE Users AS u
SET UserPoints=oo.UserPoints
FROM (
	SELECT
	    SUM(f.FeedbackPoints) AS UserPoints,
	    f.UserID AS UserID
	FROM Feedback AS f
	GROUP BY f.UserID
) AS t
WHERE t.UserID=u.UserID

(or COUNT, and of course depending on what the relevant bits of your schema look like).

johanafm

While I do not use PostgreSQL, that does not look right. Considering the use of the reference "oo" without it being defined, I'm guessing something fell away in the editing. Either way, I'm guessing that the following is what Weedpacket is after. And it does work with mysql

UPDATE user as u
SET u.points = (
  SELECT COUNT(*) as POINTS
  FROM feedback
  WHERE u.id = user_id
  GROUP BY user_id
)

But this would require one subquery per user. I'd go with a join instead

UPDATE user as u
INNER JOIN (
  SELECT COUNT(*) as points, user_id
  FROM feedback
  GROUP BY user_id
) t ON u.id = t.user_id
SET u.points = t.points

bradgrafelman

I'm guessing the "oo" in Weedpacket's post was meant to be "t" (in which case the query would appear to be valid and work on MySQL... at least at a glance).