Solved

Insert/Update/Delete From Two SQL Server Tables

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…

776 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