🚀 go-pugleaf

"Dan K" <koodanospam@icsusa.com> wrote in message 
news:47d18e36$1@newsgroups.borland.com...
>
> I need to write some SQL to flip the values in a table or two.  Here's an
> example of what I want to do.  It works, but I'm not sure if it's safe. 
> All
> of our users are using Interbase 7.1.  In my example, I'm flipping tax2 
> with
> tax1 and using tax3 as a placeholder.  I know that tax 3 is null, so that
> part is safe.  Will the order of operation always be the same?
>
> update taxes set
>  tax_tax3desc = tax_tax1desc,
>  tax_tax1desc = tax_tax2desc,
>  tax_tax2desc = tax_tax3desc,
>  tax_tax3desc = null
> where storenumber_Id = 3
>
> Strange looking, but it seems to work.


It's *probably* safe, however SQL is not a procedural language and I'm not 
aware of anything in either the SQL standard or in InterBase that guarantees 
a statement will executed as such. Therefore there is at least some risk in 
depending on such behaviour.

A much better idea would be to use a stored procedure for operations such as 
this, and that has the added benefit of not needing a dummy field in the 
record definition to accomodate such operations. The exact process above can 
be done in a proc as follows (not tested of course):

create procedure SwapTaxes (
  storeid integer
)
as
declare variable tax1desc varchar(30); // whatever correct definition is
declare variable tax2desc varchar(30); // whatever correct definition is
begin
  select tax_tax1desc, tax_tax2desc from taxes
  where storenumber_Id = :storeid
  into tax1desc, tax2desc;

  update taxes set
    tax_tax1desc = :tax2desc,
    tax_tax2desc = :tax1desc
  where storenumber_Id = :storeid;
end;

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca)