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.
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.
Introduction to ASP Ebook Download this ebook to get everything you need to know about learning ASP - from a step by step tutorial to function lists, sample code, common errors and solutions, and much more! 101 pages. |
You Should Also Read:
SQL Updating
Related Articles
Editor's Picks Articles
Top Ten Articles
Previous Features
Site Map
Content copyright © 2023 by Lisa Shea. All rights reserved.
This content was written by Lisa Shea. If you wish to use this content in any manner, you need written permission. Contact Lisa Shea for details.