General discussion

Locked

SQL UPDATE multiple tables

By Mr. Origami ·
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"))

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL UPDATE multiple tables

by jeng In reply to SQL UPDATE multiple table ...

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

Collapse -

SQL UPDATE multiple tables

by Mr. Origami In reply to SQL UPDATE multiple table ...

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?

Collapse -

SQL UPDATE multiple tables

by aong In reply to SQL UPDATE multiple table ...

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.

Collapse -

SQL UPDATE multiple tables

by Mr. Origami In reply to SQL UPDATE multiple table ...

Poster rated this answer

Collapse -

SQL UPDATE multiple tables

by Mr. Origami In reply to SQL UPDATE multiple table ...

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums