?
Solved

SQL Update Statement Using 2 Tables

Posted on 2009-02-17
6
Medium Priority
?
380 Views
Last Modified: 2012-05-06
I am attempting to update two tables.  I have a parent table and child table, and when updating the parent table I also want information in the child table to be updated as well, but I cannot seem to get the update statement to work as expected.

What I am attempting here is when updating the parent table to also update the ttblSalesAppt table column txtAppts.

I already have a select statement where the information is pulling correctly from the ttblSalesAppt table, but at this point I am having to update that table seperatly in order for the select statement to show up correctly.

I hope I have provided enough information regarding this issue.

Would it be smarter to attempt a stored procedure for this also, I have never written one, but have read where this could be used for this situation.

UPDATE    tblSalesManifestGAA
SET              txtLocation = @txtLocation, dtDate = @dtDate, dtPartyTime = @dtPartyTime, txtLNameGuest = @txtLNameGuest, txtFNameGuest = @txtFNameGuest, 
                      txtProgCode = @txtProgCode, txtMktgComment = @txtMktgComment, txtBooker = @txtBooker, txtComments = @txtComments, ckShow = @ckShow, 
                      ckNoShow = @ckNoShow
FROM         tblSalesManifestGAA INNER JOIN
                      ttblSalesAppt ON tblSalesManifestGAA.pkSalesManGAAID = ttblSalesAppt.pkSalesManGAAID
WHERE     (tblSalesManifestGAA.pkSalesManGAAID = @pkSalesManGAAID)

Open in new window

ViewUpdate.bmp
0
Comment
Question by:daleimi
6 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23662353
You cannot update two tables with one statement.  You would have to use two separate statements.  
0
 

Author Comment

by:daleimi
ID: 23662396
Okay so how would this be accomplished using just a Cross Join, and updating each table as a seperate entity seperated by a " ; " or just two statements seperate within the builder such as:

Update tableA
Set       txtsomething=@txtsomething
From    tableA
Where txtsomething=@txtsomething

Update tableB
Set txtsomethingb=@txtsomethingb
From tableB
Where txtsomethingb=@txtsomethingb

Am I on the right track here, or completely off?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23662464
That's not a cross join.  A cross join is a join whereby every record from one table is joined with every record from another.  Thus producing a cartesian.

Yes, two update statements.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 25

Accepted Solution

by:
reb73 earned 1500 total points
ID: 23662505
Yes, two update statements preferably within a transaction like -

BEGIN TRAN ...
Update Table1...
Update Table2...
COMMIT

This way the parent/child tables should be in sync..
0
 
LVL 2

Expert Comment

by:yatin_81
ID: 23662535
Instead of writing any code to update the column in other table, if you want to store same value in column of other table, then it would be recommended to use relationship between two table.
What you can do is make a relationship between two tables and set cascade update. The child table will be automaitically updated when the value in parent table changes.
0
 

Author Comment

by:daleimi
ID: 23662604
Yeah that is a new concept for me, have not been working with SQL for very long, I am still trying to figure out the relationship of Primary key to Foreign key.

Are there any sites you can direct me to in order to accomplish this task.

Any help would be appreciated.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 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