|
| |
|
|
polyclay
Posts: 30
Joined: 10/3/2003
Status: offline
 |
Batch Update One Table from Another Table - 7/14/2005 14:50:48
I do a website for an organization that offers sports safety courses.
I added a new field, TitleCode, to the CourseTitle table.
I have updated each course on the Event table (which can have many courses with the same TitleCode) with its appropriate TitleCode. The primary key on the Event table is EventID.
Now I need to update all the records in the Registration table to include the new TitleCode. Each Registration record has an EventID.
Is there a Query I can run that will update each Registration record with the TitleCode from the EventID record? This would save me from having to update 1500 registrations by hand!
I could either run it directly on Access or through the web.
|
|
|
|
rdouglass
Posts: 9187
From: Biddeford, ME USA
Status: offline
|
RE: Batch Update One Table from Another Table - 7/14/2005 15:13:03
Without knowing all the specifics, I can only guess at this. Maybe something like:
<%
DIM myDSN, mySQL, myArray, conntemp, rstemp
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/myDatabase.mdb")
mySQL = "SELECT DISTINCT EventID, TitleCode FROM CourseTitle"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)
IF rstemp.eof THEN
response.write ("ERROR 127:No Data in :" & mySQL)
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
response.end
ELSE
myArray=rstemp.getrows
rstemp.close
set rstemp=nothing
FOR i = 0 TO ubound(myArray,2)
mySQL = "UPDATE Registration SET TitleCode = '" & myArray(1,i) & "' WHERE EventID = " & myArray(0,i)
conntemp.execute(mySQL)
NEXT
conntemp.close
set conntemp=nothing
END IF
%>
You'll need to change the name of myDatabase.mdb to match yours. This is assuming your DB is in the fpdb folder and the registration table is named "Registration". Also, I did not test the syntax and I'm assuming that the field TitleCode is text.
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
polyclay
Posts: 30
Joined: 10/3/2003
Status: offline
|
RE: Batch Update One Table from Another Table - 7/14/2005 15:38:31
Yes INDEED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
What an incredibly useful code!
Thanks a MILLION!!!!!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
