Solved

Insert/Update/Delete From Two SQL Server Tables

Posted on 2002-05-29
3
144 Views
Last Modified: 2010-05-02
I'm using VB6 against an SQL Server 7 database.
I'm trying to modify two different tables with one sql statement.
Can this be done or do I have to use two steps to make the changes.
I'll explain this a little more.
I bring back a recordset which is a join between two tables linked on a common key.
The recordset fills a grid and when the user clicks on a row in the datagrid, the info from both tables is brought up in another form.
Fields from both tables are editable and when I make changes such as modify and delete, I want the changes to affect both tables if data is changed.
I know this sounds confusing and you may have to see the database design so I apologize for the description.
Basically, can I do this:
UPDATE table1, table2 SET .....in one statement
or do I have to do the updates separately.
such as
UPDATE table1 SET ...
UPDATE table2 SET ...


Thanks in Advance.
John
0
Comment
Question by:jtrapat1
  • 2
3 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
ID: 7042128
No you cannot do it in a single statement, you basically have two choices:

1) Use triggers
2) Use Transactions

The simplest is to use a transaction:

Begin Transaction
Update MyTable1 Set ...
Update MyTable2 Set ...
Commit Transaction

Or rollback if the update(s) failed of course.

Alternatively you can use a trigger in each table to update the appropriate information in the other table. This is a common method when you have OLTP and DSS information in different databases or tables and you need to record some or all of the information relating to a transaction in the other table.
0
 
LVL 2

Expert Comment

by:Crin
ID: 7043025
Hi,
you can do it if you have common fields for joining.

So, use it in manner:

Update tblO2 inner join tblR2 on tblO2.ID = tblR2.ID set tblO2.strValue = "aaa", tblR2.lngValue = -1 where tblO2.intType = 10;

Sincerely,
Crin
0
 
LVL 2

Expert Comment

by:Crin
ID: 7043033
Hehe, you hurried a little and accepted wrong answer...

Sincerely,
Crin
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to the message 13 63
Input past end of file vbs script 9 69
Excel object stays open 19 65
VB6 - Convert HH:MM into Decimal 8 38
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now