Solved

Insert/Update/Delete From Two SQL Server Tables

Posted on 2002-05-29
3
145 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

920 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

12 Experts available now in Live!

Get 1:1 Help Now