insert statement in postgresql

I have a table with 8 records.

I have 4 records for Dec-17, same 4 records for Jan-18 (only yearmon changes)

I am trying to have same records for the month of Feb-18 as well. All the column values should be same, only the yearmon has to change to Feb-18.

I can write an insert statement if I have less than 5 records. Is there any other way to write a query to get the desired output?

income   score_1  score_2  rank  yearmon
     800      77      90      10   Dec-17
     900      72      44      99   Dec-17
     700      62      44      06   Dec-17
     600      55      23      06   Dec-17
     800      77      90      10   Jan-18
     900      72      44      99   Jan-18
     700      62      44      06   Jan-18
     600      55      23      06   Jan-18

Output looks like

   income   score_1  score_2  rank  yearmon
     800      77      90      10    Dec-17
     900      72      44      99    Dec-17
     700      62      44      06    Dec-17
     600      55      23      06    Dec-17
     800      77      90      10    Jan-18
     900      72      44      99    Jan-18
     700      62      44      06    Jan-18
     600      55      23      06    Jan-18
     800      77      90      10    Feb-18
     900      72      44      99    Feb-18
     700      62      44      06    Feb-18
     600      55      23      06    Feb-18

Answer

First insert all Distinct values of income, score_1, score_2, RANK combination. then update yearmon to Feb-18

INSERT INTO T
       SELECT DISTINCT income, score_1, score_2, RANK
         FROM T;

    UPDATE T
       SET yearmon = 'Feb-18'
     WHERE yearmon IS NULL;

Advertisement

Answer