add new recorded to my table with current timestamp and new status-CodePudding

I would like to add new record to my table with same previous record just modify the timestamp to current timestamp and new status , my table look like :

my expectation results is :

Any idea how , thanks in advance .

CodePudding user response：

Hmmm . . . If you want to add a "4" to all the "3"s, then:

insert into t (doc_id, time_stamp, doc_num, doc_status, num_emp)
    select doc_id, sysdate, doc_num, 4, num_emp
    from t
    where doc_status = 3;

If you want the next doc_status for each doc_id, then you need to get the most recent one and add 1:

insert into t (doc_id, time_stamp, doc_num, doc_status, num_emp)
    select doc_id, sysdate, doc_num, doc_status   1, num_emp
    from (select t.*,
                 row_number() over (partition by doc_id order by time_stamp desc) as seqnum
          from t
         ) t
    where seqnum = 1;

CodePudding user response：

You can do this

INSERT INTO TABLENAME (
COLUMN1
COLUMN2
COLUMN3
) 
SELECT
COLUMN1
COLUMN2
'YOUR VALUE' AS COLUMN 3 
FROM TABLENAME
WHERE 'YOUR FILTER HERE'

in the filter you get only the two rows you need (with a IN clause on the iD, for example), and you can specifiy the valuen you want in the select list