0

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 :

enter image description here

my expectation results is :

enter image description here

Any idea how , thanks in advance .

drali
  • 15
  • 6
  • Is the previous one always the one with the status of 3? – Gordon Linoff Sep 13 '21 at 11:07
  • What is the real issue: insert the data from some table or get *latest* records? – astentx Sep 13 '21 at 12:04
  • Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) – astentx Sep 13 '21 at 12:04

2 Answers2

1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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

Carlo Prato
  • 326
  • 4
  • 21