Inserting a select statement result into another table
От | Andrew Selle |
---|---|
Тема | Inserting a select statement result into another table |
Дата | |
Msg-id | 20000817130517.A10909@upl.cs.wisc.edu обсуждение исходный текст |
Список | pgsql-hackers |
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; Unfortunately, when I do this I get ERROR: ORDER BY is not allowed in INSERT/SELECT The select works fine aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;taskid | ?column? | now --------+----------+------------------------ 4 | 1 | 2000-08-17 12:56:00-05 3 | 1 |2000-08-17 12:56:00-05 (2 rows) It seems to me, this is something I should do. I was wondering if there is any reason why I can't do this? I've thought of a couple of workarounds but they don't seem to be very clean: 1. Read the results of the select at the application level and reinsert into the todolist table 2. Add two fields to the task table that keep track of userid and claimed. This unfortunately clutters the main task table,and it loses the ability to assign multiple people to the same task. It also requires looping at the applicationlevel I think 3. use a temporary table with a SELECT INTO statement and then copy the contents of the temporary table into the table Iwant it in todolist Below are the table creation statements for this sample... -Andy CREATE TABLE tasks (taskid int4,title varchar(64),descr text,submit datetime,done boolean ); CREATE TABLE users (userid int4,name varchar(32) ); CREATE TABLE todolist (taskid int4,userid int4,claimed datetime );
В списке pgsql-hackers по дате отправления: