Solved

Insert/Update/Delete From Two SQL Server Tables

Posted on 2002-05-29
3
151 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

690 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