| Subject | Re: [firebird-support] Update on joined tables |
|---|---|
| Author | unordained |
| Post date | 2005-07-13T21:07:46Z |
Has anyone else suggested doing this as an insert rather than an update?
In transaction 1:
delete all the rows you -would- update, based on your WHERE criteria
don't commit until transaction 2 is at least started
In transaction 2:
insert into the table the result of a join between the current data and the other table, pulling
appropriate values from each
'Course, transaction 2 would still potentially see PK violations if those constraints are in
place ...
In that case, a temporary table could be used. Create a table that looks like the one you're trying
to update; dump the join of the current data and the other data into the temporary table; delete
the rows you want to 'update' from the main table, then re-insert from the temporary table into the
main table; clean up the temp table (either delete from it or drop it, depending on whether or not
you need to do this often.)
The insert-from-select is fast (indexed and everything) and looks much more pleasant than a bunch
of independent subselects.
In either case, you wind up doing something like
insert into temp_tablea
select tablea.pk_field, tableb.field2, .... tableb.field75 from tablea inner join tableb on
tableb.pk_field = tablea.pk_field
... but don't forget any fields from tablea that you weren't planning on updating. You'll need to
copy those over too. In that respect, it's messier, as you have to deal with fields you would
otherwise leave alone. Oh, and this creates a much larger mess of deleted records to garbage-
collect. Potentially a really bad thing if you're updating a data set of, say, blobs.
-Philip
---------- Original Message -----------
From: "emel.hu" <emel@...>
To: firebird-support@yahoogroups.com
Sent: Wed, 13 Jul 2005 21:02:51 +0100
Subject: Re: [firebird-support] Update on joined tables
In transaction 1:
delete all the rows you -would- update, based on your WHERE criteria
don't commit until transaction 2 is at least started
In transaction 2:
insert into the table the result of a join between the current data and the other table, pulling
appropriate values from each
'Course, transaction 2 would still potentially see PK violations if those constraints are in
place ...
In that case, a temporary table could be used. Create a table that looks like the one you're trying
to update; dump the join of the current data and the other data into the temporary table; delete
the rows you want to 'update' from the main table, then re-insert from the temporary table into the
main table; clean up the temp table (either delete from it or drop it, depending on whether or not
you need to do this often.)
The insert-from-select is fast (indexed and everything) and looks much more pleasant than a bunch
of independent subselects.
In either case, you wind up doing something like
insert into temp_tablea
select tablea.pk_field, tableb.field2, .... tableb.field75 from tablea inner join tableb on
tableb.pk_field = tablea.pk_field
... but don't forget any fields from tablea that you weren't planning on updating. You'll need to
copy those over too. In that respect, it's messier, as you have to deal with fields you would
otherwise leave alone. Oh, and this creates a much larger mess of deleted records to garbage-
collect. Potentially a really bad thing if you're updating a data set of, say, blobs.
-Philip
---------- Original Message -----------
From: "emel.hu" <emel@...>
To: firebird-support@yahoogroups.com
Sent: Wed, 13 Jul 2005 21:02:51 +0100
Subject: Re: [firebird-support] Update on joined tables
> > I'm not sure I know what you mean by efficient work. Certainly a------- End of Original Message -------
> > statement like this looks ugly
> >
> > update tablea
> > set field2 = select field2
> > from tableb b
> > where pk_field = b.pk_field,
> > .....
> > set field75 = select field5
> > from tableb b
> > where pk_field = b.pk_field;
> >
> >
> > but in practice, the whole path and expanded record for tableb are in
> > cache, so there's not great performance penalty.
>
> Sure, but if I want use a complex select (for example with aggregate
> functions), it can't come from cache ;(
>
> Certainly I can use SP...
>
> eMeL
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>