At 03:14 PM 11/08/2005 -0700, you wrote:

>Can someone tell me if there is a better (more efficient) way to do this
>statement in firebird:
>
> UPDATE MBCONTACT SET PREFERRED = 0
>
> WHERE CONTACTKEY in (select PERSONCONTACTKEY from mbpersoncontact where
>PERSONKEY = :PKEY)
>
>
>There is two tables, MBCONTACT contains contact detail entries (phone,
>mobile, email etc) with one set as the preferred method.
>
>The other table (MBPERSONCONTACT) links these contact entries with the
>person.
>
>The SQL above is supposed to reset the preferred contact, given the person
>ID.

update mbcontact c
set preferred = 0
where exists (
select 1 from mbpersoncontact pc
where
c.contactkey = pc.personcontactkey
and pc.personkey = :pkey)

>
>
>Without using a stored procedure, is there a better way than using the
>subquery, some sort of join?

No. An update statement has to update rows in a single table - it can't
operate on joined sets. For a huge table, going the SP route would be more
efficient.

>The ibo query is being dynamically created in the software.

The query is being dynamically created? or the value for the parameter is
being applied dynamically to a prepared statement? If not the latter, then
your application is creating a lot of unnecessary overhead, both locally
and on the wire, if it's re-inventing the same insert statement for every
hit on the mbcontact table.

./heLen