0

Since this is complicated I'll give an example of what I'm trying to attempt:

In the first table, User Data, I have user's and their saved game files (savefile's). Each user can be associated with multiple saved game files. The second table is Scores of all the saved game files on the server. It has a row for each savefile, and also a timestamp of when the game was saved, the score at that point, and the title of the game.

Now say I want to reset to a zero score all users' most recent files for each game title. This means I need to UPDATE Scores SET score=0 WHERE user=x AND title=y AND timestamp = (SELECT max(timestamp) FROM Scores where user=x and title=y);.

However, I'm unsure of how to do this with user not being directly in the Scores table. Since the User Data and Scores tables are joined by a unique savefile, though, I know there should be a way to do this. Also, if anybody could recommend to me the best way to handle this iteration so that the statement executes for ALL user, title pairs, that would be helpful!

Logistics - using SQL and Python via mysqldb.

Thanks!

user2048643
  • 283
  • 1
  • 3
  • 14

1 Answers1

1

This is a SQL only solution hope it helps. You might have to play around with this somewhat but the following SQL Code should do the trick for you... i think

UPDATE scores 
  set score = 0
FROM
scores s
   INNER JOIN users u
      on s.savefileid = u.savefileid -- primary key foreign key relationship here
      AND s.timestamp = (SELECT max(timestamp) FROM Scores ss where ss.savefileid = s.savefileid )
WHERE user=x 
  AND title=y

to see a list of records before you update them 

Select * 
FROM
scores s
   INNER JOIN users u
      on s.savefileid = u.savefileid -- primary key foreign key relationship here
      AND s.timestamp = (SELECT max(timestamp) FROM Scores ss where ss.savefileid = s.savefileid          )
WHERE user=x 
  AND title=y
Wombelite
  • 302
  • 1
  • 6
  • Can you explain what's going on with the time stamp here? – user2048643 Nov 19 '13 at 01:57
  • 1
    there two parts to the inner join, the actual primary / foreign key relationship which on its own will return multiple records for each user based on the savefileid, and the second part limits the data subset to the max timestamp selected in the inline select. returning you one row per user which is the row that contains the max timestamp, its an interesting one to try and explain. :) – Wombelite Nov 19 '13 at 02:13
  • So the time stamp that this selects as maximum, would that still be the maximum _given_ user=x and title=y, even though those aren't announced until later? (Also, where you've done inner join users - that refers to the table, not the column, right?) – user2048643 Nov 19 '13 at 02:57
  • 1
    Correct on both counts, i probably need a clearer idea of the columns you have in your table and the names of the columns that form your relationship between the two tables to make solid example, (the reference to savefileid might be misleading) – Wombelite Nov 19 '13 at 03:12
  • Ok, thanks! I'm pretty sure this will do what I need then. One more question, if you don't mind, when you use "s" and "u", are those just aliases for scores and users? Could they be anything? I've seen this done in other examples as well but found them to confusing to figure out. – user2048643 Nov 19 '13 at 04:13
  • Yes S and U are aliases and you can use anything, just make sure you run that select to check the data you are updating. – Wombelite Nov 19 '13 at 04:55