Update 2 tables in a single update query

[Nomi]

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.

[David Satz]

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

[Kampfflieger]

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