Update Table Based Off Temporary Table

Question

In a postrgres system, I have created a temporary table that holds a foreign key value to another table. I am hoping to update another table with information from that temporary table based on the foreign key value in the table I want to update.

My thought was to use an update query such as:

update playerdata set "slotID"=temp1."gameID" from temp1 where playerdata."PlayerID"=playerdata."PlayerID";

However when I do this update, I get the same gameID for every slotID in the table when they should be different based off of playerID.

For example if temp holds:

playerid gameID
1         10
2         11

the result of the player data table update is:

playerid     slotID
1             10
2             10

and I want the result to be:

playerid     slotID
1             10
2             11

I have also worked with a join statement with the temporary table and it works as expected, the gameID's are with their corresponding playerID. select w."PlayerID", t."gameID" from playerdata as w, temp1 as t where t."PlayerID" = w."PlayerID";

Is there a way to update the player data table's slotid based off the corresponding temporary playerID and gameID?

Answer 1

Your join condition is incorrect in the first query:

update playerdata
    set "slotID"=temp1."gameID"
    from temp1
    where playerdata."PlayerID" = temp1."PlayerID";
----------------------------------^