SQL Update Statement Using 2 Tables

Posted on 2009-02-17
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

Question by:daleimi
    LVL 39

    Expert Comment

    You cannot update two tables with one statement.  You would have to use two separate statements.  

    Author Comment

    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?
    LVL 39

    Expert Comment

    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.
    LVL 25

    Accepted Solution

    Yes, two update statements preferably within a transaction like -

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

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

    Expert Comment

    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.

    Author Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    732 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

    17 Experts available now in Live!

    Get 1:1 Help Now