Is this safe?

Hi,

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.

Thanks!

Dan K

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)
>

"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)