🚀 go-pugleaf

Thanks for the reply.  This is a one time "flip", so I was hoping to make it
simple.  The stored procedure is a good way to go, even if I drop it after
I'm done.  Better safe than sorry!

Thanks,

Dan

"Wayne Niddery (TeamB)" <wniddery@chaffaci.on.ca> wrote in message
news:47d1cc21$1@newsgroups.borland.com...
> "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)
>