Web Development

Ask something
Discussion Locked

SQL UPDATE multiple tables

0 helpful
5 replies

by mr. origami

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How can I update multiple tables (ASP/VBSCRIPT/ACCESS)? Let's say I have 2 tables, tblFood and tblDrink with the following fields:
tblDrink = drinkID, drinkName
tblFood = foodID, foodType, foodName, alcoholFlag

(Note the tables are not related right now. This is an example - not my real project!)

"foodType" indicates breakfast, lunch, dinner
"alcoholFlag" is currently set to "no" across entire table

Currently, tblDrink lists only soda and tblFood lists all sorts of stuff. If I UPDATE tblDrink to change "creamsoda" to "beer", I also want to set "alcoholFlag" to "yes" for all foods of type "dinner".

UPDATE tblDrink SET drinkName="beer" WHERE drinkName="creamsoda"

works to update the Drink table - but how do I ALSO set the alcoholFLag in tblFood to a "yes" for all foodType of "dinner"?

Is there such thing as something like this?:
UPDATE tblDrink,tblFood SET ((tblDrink.drinkName="beer" WHERE tblDrink.drinkName="creamsoda") AND (tblFood.alcoholFlag="yes" WHERE tblFood.foodType="dinner"))

5 replies

by jeng

AFAIK, SQL UPDATE doesn't support the syntax above (nor multiple tables UPDATE). You will need to make two separate SQL query statements. For example,

SELECT COUNT(*) FROM tblDrink WHERE drinkName="creamsoda"

if matches exist then
UPDATE tblDrink SET drinkName="beer" WHERE drinkName="creamsoda"
UPDATE tblFood SET alcoholFlag="yes" WHERE foodType="dinner"

You may also want to wrap the queries in a exclusive transaction lock (if necessary).

If these tables are joined, you can probably use subselects in your UPDATE condition statement, but you will still need two separate UPDATE queries to perform the two distinct table updates.

Suggestion: Per your example, perhaps put the alcohol flag in the tblDrink table once and have tblFood linked to tblDrink? (Or another table, e.g., tblDinner linked to tblDrink and tblFood?)

by mr. origami

Maybe my question is more an ASP question then. How do I construct my ASP to execute 2 UPDATE statements in a row? I usually do something like:
dim strSql
strSql = "UDATE... statement...here..."
set Command1 = Server.CreateObject("ADODB.Command")Command1.ActiveConnection = myConnectionString

Command1.CommandText = strSql
Command1.Execute()

But how would I execute 2 UPDATES in a row?

by aong

What you do is you execute 2 sqls one after another as per jeng's answer. Here is a sample code to do this:

dim strSql, objDBConn

Set objDBConn = Server.CreateObject("ADODB.Connection")
objDBConn.ConnectionString = myConnectionString

' Open DB Connection
objDBConn.open

'Execute 1st SQL Statement
strSql = "UPDATE 1st table statement..."
objDBConn.Execute(strSQL)

'Execute 2nd SQL Statement
strSql = "UPDATE... statement...here..."
objDBConn.Execute(strSQL)

'close any connections and release ADO COM object
objDBConn.close
set objDBConn = nothing

Note: You may do this with command objects as well.

This discussion is closed to new replies.