Subject RE: [IB-Conversions] update using data from multiple tables
Author Claudio Valderrama C.
> -----Original Message-----
> From: Andrew Gould [mailto:andrewgould@...]
> Sent: Lunes 6 de Noviembre de 2000 15:26
>
> update table1
> set table1.column1 = table2.column3
> from table1, table2
> where table1.column2 = table2.column2;

This is an invalid syntax in IB. You have two options:

1.- Normal select statement. You can do a sub-select for each field that you
want to change:
update table1
set column1 = (select column3 from table2
where table2.column2 = table1.column2)

2.- Write a stored proc, this construction is usually the fastest. This is
the body:
FOR select table1.rdb$db_key, table2.column3
from table1 join table2 on
table1.column2 = table2.column2
INTO :dbk, :col3
DO
update table1 set column1 = :col3
where rdb$db_key = :dbk;

If you feel confused by this syntax, go to my site and read about
"rdb$db_key" in the "documentation" section.

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://www.cvalde.com