On 2015-05-19 o 13:59, millsjack14@... [firebird-support] wrote:

> Is there a single query that allows multiple row updates from multiple row selects (e.g. like Copy & paste)
> i.e. Select row 1 column 7 & update row 101 column 7
> Select row 2 column 7 & update row 102 column 7
> Etc for say 20 rows
>
> Column 1 (row) is ‘TAG_NO’ pk ascending, Column 7 Is’ NAME’
>
> At the moment I have used Execute block that works ok but wonder if possible using a query
>
> execute block -- Copy 19 words from MMH UOP to another UOP section
> as
> declare variable i integer;
> begin
> i = 2433; -- start Tag No of MMH UOP
> while (i < 2452) -- 19 words to be copied that all UOP's have. Phase No, Brand etc.
> do
> begin
> update analog a1 set a1.name = (select a2.name from analog a2 where a2.tag_no = :i)
> where a1.tag_no = :i + 1238; -- 1238 is Offset from 2433 to start address of CIP UOP = 2433 +1238
> i = i+1;
>
> end
> end;
>
> By accident I came up with a query that appears to work but have not finished fully testing & understanding, so would like the help of this skilled group for a known solution.

Usually the simplest solutions work best, so I would go for:

update ANALOG a
set a.NAME=(select NAME from ANALOG where TAG_NO=a.TAG_NO-1238)
where a.TAG_NO >= 3671;

(3671 = 2433 + 1238)

Eventually you can further filter the rows being updated by adding more
terms to the "where" clause.

regards
Tomasz

>
> Thanks for any help
> Jack
>
>

--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__