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