?
Solved

Insert/Update/Delete From Two SQL Server Tables

Posted on 2002-05-29
3
Medium Priority
?
154 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 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…
Suggested Courses

649 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