Advanced SQL Update Command: Using Two Tables
There are times that you need to update one SQL database table, and want to use values from another table to do the update with. Here is how you would do that in ASP.
First, the scenario to make this easier to understand. Let's say you are moving servers for some reason. You had a "traffic" table on the old server that had the fields page_id and hit_count. You also have a "traffic" table on the new server, with those same fields. Now that you are done moving, you want to combine those two traffic figures together so you have one table with your total traffic.
First, you would move the data from the old server into the new server, into a table called "traffic_old". So the two tables are side by side on the new server. The SQL syntax to add those old traffic numbers into the new one would be:
update traffic
set hit_count = traffic.hit_count + to.hit_count
from traffic_old to
where traffic.page_id = to.page_id;
That command will update the traffic table so that each page ID's value is now equal to its original value plus the matching value from traffic_old.
For the full ASP shell, read the Intro to Updating in ASP
If you're using character/string values, be sure to read about Handling Apostrophes in Input Fields to make sure your input fields are ready for use in SQL.
SQL Command Listing