betoban2007 wrote:

> Hi, i need update the fields of a table with data from another table.
> In others data bases i do thus:
>
> UPDATE tbl_equipos
> SET tbl_equipos.precio_compra = m.valor
> FROM tbl_equipos INNER JOIN TBL_MODELO_EQUIPOS m ON
> tbl_equipos.modelo = m.ID_MODELO_EQUIPO
> WHERE ((tbl_equipos.ESTADO = 1) AND (m.ID_MODELO_EQUIPO = 7));
>
> In Firebird i do whit 2 sub-selects thus:
>
> UPDATE tbl_equipos
>
> SET tbl_equipos.precio_compra = (SELECT m.valor FROM
> TBL_MODELO_EQUIPOS m WHERE((tbl_equipos.modelo =
> m.ID_MODELO_EQUIPO) AND (m.ID_MODELO_EQUIPO = 7)))
>
> WHERE(tbl_equipos.ESTADO = 1)
> It query found correctly. But i feel very bad to do 2 sub-select in
> one query.
>
> Exist a more efficient way of doing an update multi tables?
> thak...

That is how things are done in Firebird. When updating one field, I'd
say 'the Firebird way' is no more complex than the 'Betoban way', though
I do admit that doing things the Firebird way does make things verbose
if you try to update more than one field. Still, I think it is mainly
the written DML that gets more verbose, I don't think it slows down very
much if you add a few extra fields to be updated with similar subselects
(I think Ann has written about things being in cache so that subsequent
subselects are quick).

The only thing I can see that you can change is removing a few
parenthesis and add an alias:

UPDATE TBL_EQUIPOS e
SET e.PRECIO_COMPRA = (
SELECT m.VALOR
FROM TBL_MODELO_EQUIPOS m
WHERE e.MODELO = m.ID_MODELO_EQUIPO
AND m.ID_MODELO_EQUIPO = 7)
WHERE e.ESTADO = 1

But this just makes the query easier to read, I don't think it makes the
execution of the query any slower or quicker. It may be because I use
Firebird too much, but I find the above statement at least as easy to
read as your INNER JOIN.

Set