Update 2 tables in a single update query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Update 2 tables in a single update query

  1. #1
    Nomi Guest

    Update 2 tables in a single update query


    hi friends,
    how do i update two tables column in a single query?

    i can update single table columns from multiple tables conditions,

    eg: update table1 set table1.field1,table1.field2 from table2,table3
    where table1.field1=table2.field1 and table2.field1=table3.field1

    now

    i want to update columns of different tables table1.field1 and table2.field2


    how do i update two tables columns in a single query.





  2. #2
    David Satz Guest

    Re: Update 2 tables in a single update query

    you cannot update 2 tables in 1 SQL statement. You can write a stored
    procedure to update both tables. And have the 2 UPDATEs wrapped within a
    transaction.

    SQL Server ex.:

    BEGIN TRANSACTION

    UPDATE table1
    SET col1 = @values1
    WHERE...

    IF @@error <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR("Unable to update table1", 16, 1)
    RETURN
    END
    UPDATE table2
    SET col2 = @values2
    WHERE...

    IF @@error <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR("Unable to update table2", 16, 1)
    RETURN
    END

    COMMIT TRANSACTION

    --
    HTH,
    David Satz
    Principal Web Engineer
    Hyperion Solutions
    { SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "Nomi" <yesnomiyes@hotmail.com> wrote in message
    news:3d0d74bd$1@10.1.10.29...
    >
    > hi friends,
    > how do i update two tables column in a single query?
    >
    > i can update single table columns from multiple tables conditions,
    >
    > eg: update table1 set table1.field1,table1.field2 from table2,table3
    > where table1.field1=table2.field1 and table2.field1=table3.field1
    >
    > now
    >
    > i want to update columns of different tables table1.field1 and

    table2.field2
    >
    >
    > how do i update two tables columns in a single query.
    >
    >
    >
    >




  3. #3
    Join Date
    Jan 2007
    Posts
    1
    BEGIN TRANSACTION

    UPDATE safi_factura_venta
    SET cod_empresa= 1
    WHERE num_factura=14947

    IF @@error <> 0
    BEGIN
    ROLLBACK TRANSACTION

    RETURN
    END
    UPDATE safi_factura_venta_detalle
    SET cod_empresa= 1
    WHERE num_factura=14947

    IF @@error <> 0
    BEGIN
    ROLLBACK TRANSACTION

    RETURN
    END

    COMMIT TRANSACTION

    returns

    Server: Msg 547, Level 16, State 1, Line 1
    UPDATE statement conflicted with TABLE REFERENCE constraint 'FK_SAFI_FACTURA_VENTA_DETALLE_SAFI_FACTURA_VENTA'. The conflict occurred in database 'SAFI', table 'SAFI_FACTURA_VENTA_DETALLE'.
    The statement has been terminated.

    I'm using SQL server 8 and Windows XP

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