Re: Inserting a select statement result into another table

At 09:34 18/08/00 +1000, Chris Bitmead wrote:
>
>He does ask a legitimate question though. If you are going to have a
>LIMIT feature (which of course is not pure SQL), there seems no reason
>you shouldn't be able to insert the result into a table.

This feature is supported by two commercial DBs: Dec/RDB and SQL/Server. I
have no idea if Oracle supports it, but it is such a *useful* feature that
I would be very surprised if it didn't.


>Ben Adida wrote:
>> 
>> What is the purpose you're trying to accomplish with this order by? No
matter what, all the
>> rows where done='f' will be inserted, and you will not be left with any
indication of that
>> order once the rows are in the todolist table.

I don't know what his *purpose* was, but the query should only insert the
first two rows from the select bacause of the limit).

>> Andrew Selle wrote:
>> 
>> > Alright.  My situation is this.  I have a list of things that need to
be done
>> > in a table called tasks.  I have a list of users who will complete
these tasks.
>> > I want these users to be able to come in and "claim" the top 2 most
recent tasks
>> > that have been added.  These tasks then get stored in a table called
todolist
>> > which stores who claimed the task, the taskid, and when the task was
claimed.
>> > For each time someone wants to claim some number of tasks, I want to
do something
>> > like
>> >
>> > INSERT INTO todolist
>> >         SELECT taskid,'1',now()
>> >         FROM tasks
>> >         WHERE done='f'
>> >         ORDER BY submit DESC
>> >         LIMIT 2;

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/