Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update Db only when information has changed

Posted on 2008-06-21
4
Medium Priority
?
259 Views
Last Modified: 2010-03-20
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?
0
Comment
Question by:DColin
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:jgoeders
ID: 21837533
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.
0
 

Author Comment

by:DColin
ID: 21837989
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.
0
 
LVL 2

Accepted Solution

by:
jgoeders earned 2000 total points
ID: 21839101
I see what you're looking for.

First check if a record exits:

set rs = currentdb.openRecordset ("SELECT * FROM myTable WHERE TEAMA = ... AND TEAMB = ... AND SCOREA = ... AND SCOREB = ...")
IF RS.EOF THEN 'RECORD ISN'T THERE YET
    CURRENTDB.EXECUTE("INSERT INTO MYTABLE (TIME, TEAMA, TEAMB, SCOREA, SCOREB) VALUES (NOW(),...,...,...,...)
ELSE
'PERFORM THE UPDATE CODE I PROVIDED ABOVE
END IF
0
 

Author Comment

by:DColin
ID: 21840046
jgoeders:

Thank you your solution works great.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question