Re: Inserting old value into a column

surfbot_at_rocketmail.com (R. Santiago) wrote in message news:<f28d4dcd.0310051246.610faf86_at_posting.google.com>...
> I have a table cc_rd_user_questions with the following columns:
>
> NUM NUMBER (25) DEFAULT 1 NOT NULL,
> COMPANY_ID NUMBER (15) NOT NULL,
> PROJ_ID NUMBER (15) NOT NULL,
> SEQ NUMBER (25) NOT NULL,
> QUEST_ID NUMBER (15) NOT NULL,
> RESPONSE VARCHAR2 (40),
> CREATE_DATE DATE NOT NULL,
> LAST_UPDATE DATE NOT NULL
>
> This table contains the responses to questions found on a form. The
> SEQ column is incremented each time a new row is inserted for a
> specific company and specific project). If during an insert the new
> RESPONSE column is null, what is the best method to populate the
> RESPONSE column with the previous RESPONSE column value (the previous
> row where COMPANY_ID, PROJ_ID and QUEST_ID are the same but SEQ = SEQ
> - 1)? Should I use a trigger (I forsee a "mutating" table problem), a
> temp table or SQL?
>
> Thanks in advance!
>
> - Rey

is there a reason why you don't want to just do something like

insert ... select ... decode(:new_response, null, (select response from cc_rd_user_questions where <what you said>), :new_response) from dual ?

however, what happens if that previous SEQ - 1 answer gets updated in the future? do you need to look at the following answer and perhaps change that as well? and could that cause a chain of new updates? maybe it would be easier if a null RESPONSE was understood to mean "see above" at query time, rather than filling it in at insert time. Received on Mon Oct 06 2003 - 23:56:06 CEST