Updating 2(two ) tables at a time in SQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Updating 2(two ) tables at a time in SQL

  1. #1
    Santosh Guest

    Updating 2(two ) tables at a time in SQL


    Hi !,

    I have 2 tables .

    Table1 Table2
    ------ ------
    ID (primary key) ID (foreign key)
    Name Address

    I want to update both the ID's in the tables. (NB. Primary key violation
    would be taken care)

    The following sql query was works perfect in Access databases.

    UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET Table1.ID ="200",
    Table2.ID = "200" WHERE Table2.ID="1000"


    But when I create the same table with the same structure in SQL 7 . Its
    not allowing me to update the tables.

    I want to know whether SQL Server allows us to update more than one table
    at a time and is anything more to be done to the above sql query to run on
    SQL server.

    Please Help.

    Thanks
    Santosh



  2. #2
    James T. Stanley Guest

    Re: Updating 2(two ) tables at a time in SQL

    Just use a transaction:

    Begin Tran
    Update Table1 Set ID="200" Where ID="1000"
    Update Table2 Set ID="200" Where ID="1000"
    Commit Tran

    "Santosh" <santosh@focuite.com> wrote in message
    news:3a9b4eb6$1@news.devx.com...
    >
    > Hi !,
    >
    > I have 2 tables .
    >
    > Table1 Table2
    > ------ ------
    > ID (primary key) ID (foreign key)
    > Name Address
    >
    > I want to update both the ID's in the tables. (NB. Primary key violation
    > would be taken care)
    >
    > The following sql query was works perfect in Access databases.
    >
    > UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET Table1.ID

    ="200",
    > Table2.ID = "200" WHERE Table2.ID="1000"
    >
    >
    > But when I create the same table with the same structure in SQL 7 . Its
    > not allowing me to update the tables.
    >
    > I want to know whether SQL Server allows us to update more than one table
    > at a time and is anything more to be done to the above sql query to run on
    > SQL server.
    >
    > Please Help.
    >
    > Thanks
    > Santosh
    >
    >




  3. #3
    Thomas Böhm Guest

    Re: Updating 2(two ) tables at a time in SQL


    Hi James,

    this does not work if you have referential integritity.
    (Foreign Key constraints.)
    The first Update would cause a violation. The following
    should do it:

    Begin Tran
    INSERT Table1 (ID, Col1, Col2, ...)
    SELECT (200, Col1, Col2, ...)
    FROM Table1 WHERE ID = 1000

    Update Table2 Set ID="200" Where ID="1000"

    DELETE FROM Table1 WHERE ID = 1000
    Commit Tran

    Kind regards

    Thomas

    "James T. Stanley" <jstanley@powerwayinc.com> wrote:
    >Just use a transaction:
    >
    >Begin Tran
    > Update Table1 Set ID="200" Where ID="1000"
    > Update Table2 Set ID="200" Where ID="1000"
    >Commit Tran
    >
    >"Santosh" <santosh@focuite.com> wrote in message
    >news:3a9b4eb6$1@news.devx.com...
    >>
    >> Hi !,
    >>
    >> I have 2 tables .
    >>
    >> Table1 Table2
    >> ------ ------
    >> ID (primary key) ID (foreign key)
    >> Name Address
    >>
    >> I want to update both the ID's in the tables. (NB. Primary key violation
    >> would be taken care)
    >>
    >> The following sql query was works perfect in Access databases.
    >>
    >> UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET Table1.ID

    >="200",
    >> Table2.ID = "200" WHERE Table2.ID="1000"
    >>
    >>
    >> But when I create the same table with the same structure in SQL 7 . Its
    >> not allowing me to update the tables.
    >>
    >> I want to know whether SQL Server allows us to update more than one table
    >> at a time and is anything more to be done to the above sql query to run

    on
    >> SQL server.
    >>
    >> Please Help.
    >>
    >> Thanks
    >> Santosh
    >>
    >>

    >
    >



  4. #4
    Vik Mohindra Guest

    Re: Updating 2(two ) tables at a time in SQL


    Hi Santosh,

    This is a very common problem and for the solution, please see the following
    Q-article: Q142480 at http://support.microsoft.com

    The cascade update and delete functions have been introduced in SQL Server
    2000.

    Cheers,

    Vik

    "Santosh" <santosh@focuite.com> wrote:
    >
    >Hi !,
    >
    >I have 2 tables .
    >
    >Table1 Table2
    >------ ------
    >ID (primary key) ID (foreign key)
    >Name Address
    >
    >I want to update both the ID's in the tables. (NB. Primary key violation
    >would be taken care)
    >
    >The following sql query was works perfect in Access databases.
    >
    >UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET Table1.ID ="200",
    >Table2.ID = "200" WHERE Table2.ID="1000"
    >
    >
    >But when I create the same table with the same structure in SQL 7 . Its


    >not allowing me to update the tables.
    >
    >I want to know whether SQL Server allows us to update more than one table
    >at a time and is anything more to be done to the above sql query to run

    on
    >SQL server.
    >
    >Please Help.
    >
    >Thanks
    >Santosh
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center