Subject Re: [firebird-support] Re: Update with data FROM a table
Author Ann W. Harrison
m_formigoni wrote:
>>update table1 t1
>>set t1.field1 = (select t2.field1 from table2 t2 where =,
>>t1.field2 = (select t2.field2 from table2 t2 where =
>>This will either work or throw an error "multiple rows in singleton
>>select" if there is more than one matching record in table2.
> Yes, it works fine, but I'm looking for a way that I don't need to do
> "n" selects in the table2 (imagine updating 30 fields in table1 from
> table2, its costs goes very high).

A stored procedure may be the right answer, but (aside from the serious
ugliness) the multiple selects will be much faster than you expect.
After the first one, all the pieces will be in cache and the retrieval
is very cheap.