Rank Tip was: [thelist] CF: Dynamic variables and Insert Statements

Joshua OIson joshua at alphashop.net
Thu May 10 17:51:50 CDT 2001


That's a good point.  Indexes are *usually* helpful in avoiding table scans,
so it's a good idea to look to them when optimizing.  There's a whole field
of study on that issue, so I won't go into depth, but thank you for the

Rudy reminded me off-list that some databases don't allow you to insert into
the same table from which you select.  So, this may be the case if you are
trying to do it this way and cannot seem to get it to work.  I am curious as
to which db's fall under this restriction, if anybody is so inclined to find


----- Original Message -----
From: "Judah McAuley" <judah at alphashop.com>
Subject: Re: Rank Tip was: [thelist] CF: Dynamic variables and Insert

> At 06:01 PM 5/10/01 -0700, joshua wrote:
> >3.  My personal favorite, which doesn't use two separate queries OR a
> >trigger.
> >
> >   You do this one as
> >
> >   INSERT INTO mytable
> >   (user_id, field1, field2, field3, rank)
> >   SELECT
> >    1, 'constant1', 'constant2', 'constant3',
> >    COALESCE(Max(rank), 0) + 1
> >   FROM mytable
> >   WHERE user_id = 1
> And if I understand correctly (a point sometimes in dispute), if you
> an joint index on the column 'rank' and the column 'user_id', then the
> Max(rank) can be done without a table scan to find the maximum rank for a
> given user.
> Judah

More information about the thelist mailing list