CODEWITHSUNDEEP
sqljoin
ardavar
ardavar

Reputation: 520

Updating one table Using data from another table in SQL

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

Upvotes: 0

Views: 1185

Answers (3)

HarrisonJackson
HarrisonJackson

Reputation: 372

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'

Upvotes: 0

Pirion
Pirion

Reputation: 519

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;

Upvotes: 0

rivarolle
rivarolle

Reputation: 1538

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

Upvotes: 2

Related Questions