Subject | RE: [firebird-support] Efficient update on small subset of LARGE table? |
---|---|
Author | Svein Erling Tysvær |
Post date | 2012-12-13T08:27:19Z |
You could try something like (not tested):
EXECUTE BLOCK AS
DECLARE VARIABLE MyDbKey char(8);
BEGIN
for select LrgTbl.rdb$db_key
from "LargeTable" LrgTbl
inner join "Uppgiftshållare" UH on UH."ECO_ID" = LrgTbl."Hållare"
inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = UH."Hanterare"
where LrgTbl."ÄrNull" = 0
and UH."Propertynamn" in ('This', 'That', 'Other', 'Extra')
and Uhant."ColX" = 'Whatever' into :MyDbKey do
begin
update LargeTable
set <whatever>
where rdb$db_key = :MyDbKey;
end
END
Using RDB$DB_KEY is probably quicker than using the primary key (ECO_ID), though I use it very rarely, so don't be surprised if there's some error above.
HTH,
Set
EXECUTE BLOCK AS
DECLARE VARIABLE MyDbKey char(8);
BEGIN
for select LrgTbl.rdb$db_key
from "LargeTable" LrgTbl
inner join "Uppgiftshållare" UH on UH."ECO_ID" = LrgTbl."Hållare"
inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = UH."Hanterare"
where LrgTbl."ÄrNull" = 0
and UH."Propertynamn" in ('This', 'That', 'Other', 'Extra')
and Uhant."ColX" = 'Whatever' into :MyDbKey do
begin
update LargeTable
set <whatever>
where rdb$db_key = :MyDbKey;
end
END
Using RDB$DB_KEY is probably quicker than using the primary key (ECO_ID), though I use it very rarely, so don't be surprised if there's some error above.
HTH,
Set