Hello all.

Please help me with that:

/* less than 30000 records*/
create table catalogue
(id integer not null primary key,
partnumber varchar(30),
last_location integer
);

/* about 200000 records */
create table transfers (
id integer not null primary key, /* autoincrement by trigger using
GEN_ID() */
catalog_id integer references catalogue (id),
location integer not null
/* ,... */
);

I'm trying to update catalogue.last_location according last record of
transfers:

update catalogue
set last_location =
(select location from transfers x where x.catalog_id = catalogue.id
and x.id =
(select max(id) from transfers cx where cx.catalog_id = x.catalog_id))

that update hangs. I've discovered that above subselects use natural join...
Pure select is too heavy, but it works fine if looks like :

select x.location from tranfers x join transfers cx on x.catalog_id =
cx.catalog_id
group by x.location
having x.id = max(cx.id)

Is it better way to do such updates ?

Thanks ahead

Andrew