0

I'm making a basketball stats database, and have a table in SQL with the player's totals from all of their games combined. I'm having trouble adding to it, however. I use a temporary table for each game, and then I need to add all of the temporary values to the player's total table. How would I do this? I have tried many methods, each of which throw an error. Here are a few:

(live_players is the temporary table, and here I'm only trying to join the points column.)

db.execute("UPDATE players JOIN live_players AS t1 ON players.player_id 
= t1.player_id SET points = points + t1.points")

db.execute("MERGE INTO players USING live_players ON players.player_id 
= live_players.player_id WHEN MATCHED THEN UPDATE 
SET points = points + live_players.points")
William Strauch
  • 11
  • 1
  • What is the error you are getting? In your statements you should alias both tables especially since both tables have a "points" column. – Jonathan Porter Dec 06 '17 at 19:20

2 Answers2

0

This should will give you the player_id and points for records in live_players that are new (not sure if that's possible) or have matching records in the players table, along with the addition of that points when player currently exist:

SELECT ST1.player_id, case when ST2.points > 0 THEN ST1.points+ST2.points Else ST1.points END as test FROM live_players ST1 LEFT JOIN players ST2 USING(player_id)

Integrating that with an UPDATE operation, depending on the unique indexing of our players table should get you where you need to be.

DDeMartini
  • 339
  • 1
  • 6
0

I think your update statement is wrong.

You do not have to make a join direct behind the update statement, it has to be put into the FROM clause. 

UPDATE table_to_update
SET what_you_want_to_set
FROM define_the_table(s)
WHERE some_restrictions (maybe with sub select)

Have a look here:

How can I do an UPDATE statement with JOIN in SQL?

or here:

SQL update query using joins

Best,

me

pydvlpr
  • 311
  • 2
  • 6