CODEWITHSUNDEEP
sqlmysql
Qiao
Qiao

Reputation: 17049

SQL whole table update with selection from another table based on the first table field

table1: name | rating | stat1 | stat2
table2: name | stat3
it is forum and site integration, so they are not normalized

I need to recount rating for users in table1.
rating = stat1 + stat2 + stat3.
stat3 is in the table2, which has common name field with table1. 

UPDATE table1 SET rating = stat1 + stat2 + 
(SELECT stat3 FROM table2 WHERE name = [name_from_table1] )

How can I insert name from table1 to use it in the table2 selection?

Upvotes: 0

Views: 559

Answers (1)

JoRobles
JoRobles

Reputation: 96

Try this:

UPDATE table1 JOIN table2 ON table1.name=table2.name SET table1.rating=table1.stat1+table1.stat2+table2.stat3

Upvotes: 2

Related Questions