Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    11

    Unanswered: Update multiple tables with single query

    How to update records in multiple tables with a single query

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Not going to happen with SQL Server unless you use triggers on the underlying table. You can update multiple columns within a single table in a single query, but not multiple tables.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please give us a bit more insight into what you're trying to accomplish. There's more than one way to skin a cat, and you've presented a large number of cats with this question.

    Please try to describe your problem in terms of the real world instead of the database. That will help us help you to find the best solution to your problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'll swipe at the grey tabby cat in the corner: Why a single query? How about a single batch instead!
    Code:
    BEGIN TRAN
    
      INSERT INTO dbo.a (a) VALUES ('a')
    
      INSERT INTO dbo.b (b) VALUES ('b')
    
    COMMIT TRAN
    And of course, don't forget your error handling!
    pre 2005 you have to use IF @@Error after each statement.
    2005 onwards gives you TRY...CATCH blocks
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And for the big ginger cat
    Code:
    DECLARE @a table (
       a int
    )
    
    DECLARE @b table (
       b int
    )
    
    INSERT INTO @a (a)
      OUTPUT inserted.a INTO @b (b)
    VALUES (1234)
    
    SELECT a
    FROM   @a
    
    SELECT b
    FROM   @b
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •