saving part of an SQL SELECT back to the table in the same transaction/ getting order

[Dr. Hawkins]

As usual, I'm trying to limit the number of transactions as the lag is
more expensive (to the user) than the transaction.

I can do something like

   SELECT name, petname FROM mytable WHERE pettype='cat' ORDER BY petname

to get a list ordered by the names of the cats.

I'd like, though, to fill that numerical order back into the column,
setting column catnum to a value for those entries.

It seems that I could do something like

WITH SELECT name, petname FROM mytable WHERE pettype='cat' ORDER BY petname
     UPDATE mytable SET catnum= <mumble>

But I'm not fining anything to get my <mumble>.

It seems kind of silly to do the SELECT, loop about forming a bunch of
UPDATE one line statements in LIveCode, and then to feed it back . . .

-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462