Updating one table Using data from another table in SQL

Question

Excuse me; I read couple of same questions but still couldn't figure out the solution.

I have two tables

photo table:

userID | photoID| score

users table:

userID | totalScore

I want to pass the photoID unique value to photo table (I mean I want to use only that value as an input variable in the query).

Then get the corresponding userID in the photos table

Then use the resulting unique userID in the users table to update totalScore such as

totalScore = totalScore + score

In another words lets say my values are

photo table:

userID | photoID| score
bob    |    5   | 500

users table:

userID | totalScore
bob    |    5000

I want the users table to look like after a query with an input of photoId=5

userID | totalScore
bob    |    5500

Hope I am clear enough.

___________________________EDIT________________________

https://stackoverflow.com/a/707668/842644

this one helped. It is possible that not every server supports update-join notation. wanted to share

score 2 · Accepted Answer · answered Apr 11 '13 at 20:46

2

update users 
set totalScore = totalScore + p.score
from users left join photo p on users.userId = p.userId
where p.photoID = 5

answered Apr 11 '13 at 20:46

rivarolle

1,528
11
18

This looks like what I want but I am having syntax errors. I will try to fix – ardavar Apr 12 '13 at 00:50
I think this part update users set totalScore = totalScore + p.score is causing trouble so far (i can work the other part by using SELECT) – ardavar Apr 12 '13 at 01:00

score 0 · Answer 2 · answered Apr 11 '13 at 20:48

Here is how to update based on another table, but you would probally want some way to determine if it has already been updated or not.

UPDATE users
SET totalScore = TotalScore+tphoto.score
FROM users tuser
INNER JOIN photo tphoto ON tuser.userid = tphotos.userid;

score 0 · Answer 3 · edited May 23 '17 at 12:18

Assuming that total score should be the sum of all of a users points from the photo table

UPDATE users 
INNER JOIN 
    (SELECT userID, SUM(photo) as 'total' FROM photo GROUP BY userID) 
    x ON x.userID=users.userID 
SET users.totalScore=x.total

I didn't have a table to run that against. I recommend using sequel pro or another tool that can give error messages if you have mysql syntax issues. I would also recommend you use an auto incrementing integer as the key you use to map between the 2 tables. Each user should have a unique integer id. That way if you have two bobs there is no problem.

Here is another similar question that might provide further insight.

Edit: if you just want to update bob add this to the end

WHERE userId='bob'

Updating one table Using data from another table in SQL

3 Answers3