asked on

Update Db only when information has changed

Hi Experts,

I am recording sports score information in a Db once per minute for the duration of a sports event. This has caused the problem that most of the records are identical apart from the time i.e:

Time TeamA TeamB ScoreA ScoreB
12:00 France Ireland 0 0
12:01 France Ireland 0 0
12:02 France Ireland 0 0
12:03 France Ireland 0 0

It would be more efficient if only a change to the score is recorded. Is it possible to do this using a SQL query?

jgoeders

UPDATE myTable SET ScoreA = " & myScoreAVariable & ", ScoreB = " & myScoreBVariable & " WHERE TeamA = 'France' AND 'TeamB = Ireland'

This is just an example of a SQL statement you would need to run. In this example I have shown how to build in the scores from variables. The same could be done with the teams. If you need to check if the teams are already entered then let me know and i'll post how to do that.

DColin

ASKER

Hi jgoeders:

Thank you for yourSQL query. I can see how this will update the score as it changes but does it record the changes as I would require, for example:

Time TeamA TeamB ScoreA ScoreB
12:00 France Ireland 0 0
12:10 France Ireland 1 0
12:23 France Ireland 2 0
12:43 France Ireland 2 1

Or would your query just keep modifying a single record to return a single entry table as below:

Time TeamA TeamB ScoreA ScoreB
12:43 France Ireland 2 1

Also it would not make the original (12:00, France, Ireland, 0, 0) entry.

ASKER CERTIFIED SOLUTION

jgoeders

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DColin

ASKER

jgoeders:

Thank you your solution works great.