Hi,
This is the relevant information in the database:
/*
Contains most of the info static on a player.
FirstUpdate and LastUpdate is a convenience to avoid JOIN if I can,
the information will be presented very often with the static info.
*/
CREATE TABLE Player (
ID integer primary key,
FirstUpdate integer,
LastUpdate integer
);/*
Contains all dynamic info on a player.
Related to Player thru PlayerUpdate.PlayerID == Player.ID.
Date is between Player.FirstUpdate and Player.LastUpdate
where PlayerUpdate.PlayerID == Player.ID.
*/
CREATE TABLE PlayerUpdate (
ID integer primary key,
PlayerID integer,
Date integer
);Now, I have removed all rows in PlayerUpdate where Date is for example 0 and want to make sure I update FirstUpdate in the Player table. However this statement fails due to the Player.ID connection which is unknown.. Is there any way to do it somewhat like this:
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID == Player.ID ORDER BY Date ASC)
WHERE FirstUpdate == 0;
Rather than this:
SELECT ID FROM Player WHERE FirstUpdate == 0; -- Returns: {1, 3, 6}
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID == 1)
WHERE ID == 1;
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID == 3)
WHERE ID == 3;
UPDATE Player
SET FirstUpdate = (SELECT min(Date) FROM PlayerUpdate WHERE PlayerID == 6)
WHERE ID == 6;
I really want to avoid the loop over all the ID's to be updated. It just seems to inefficient. If I'm unclear on some point, let me know. I'm happy to send any info or to explain more.
-- kent
