Batch Update One Table from Another Table (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

polyclay -> 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 -> 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?



polyclay -> RE: Batch Update One Table from Another Table (7/14/2005 15:38:31)

Yes INDEED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

What an incredibly useful code!

Thanks a MILLION!!!!!



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.046875