May try that with two named cursors, alike

FOR SELECT prod_name
FROM inventory FOR UPDATE
INTO :prod_name
AS CURSOR ACURSOR
DO BEGIN
UPDATE inventory SET prod_name = :prod_name
WHERE CURRENT OF ACURSOR;
END

----- Original Message -----
From: "Markus Ostenried" <macnoz@...>
To: <firebird-support@yahoogroups.com>
Sent: Saturday, March 14, 2009 1:58 PM
Subject: Re: [firebird-support] Re: how to swap two values on a
uniquelyconstrained column?


> On Sat, Mar 14, 2009 at 18:23, woodsmailbox <cosmin.apreutesei@...>
> wrote:
>>> > I know deferred constraints would solve it, but is there any other way
>>> > in the absence of it? Assigning an out-of-domain value is not
>>> > acceptable, since it breaks the domain definition. Thanks.
>>>
>>> Maybe you could swap (update) values of all other columns and leave the
>>> unique column as it is?
>>
>> Twisted :) But I can't, since one of the other columns will always have a
>> PK constraint on it.
>>
>> For example, the ORDER_INDEX column is used for custom ordering of
>> values. I want to be able to move a row's position in the dataset
>> updating that column.
>
> I hope I'm not missing the obvious here, but can't you assign a
> temporary value like so:
>
> procedure swap (pkvalue1 integer, pkvalue2 integer)
> as
> variable oderindex1 integer;
> variable oderindex2 integer;
> begin
> -- read original values
> select order_index from tbl where pk = :pkvalue1 into :oderindex1;
> select order_index from tbl where pk = :pkvalue2 into :oderindex2;
> -- temporarily assign a magic value to one of the records
> update tbl set order_index = MAX_INT where pk = :pkvalue2;
> -- update both records with swapped values
> update tbl set order_index = :oderindex2 where pk = :pkvalue1;
> update tbl set order_index = :oderindex1 where pk = :pkvalue2;
> end
>
> HTH,
> Markus
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org 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
>
>
>