CODEWITHSUNDEEP
sqlpostgresql
Chanti
Chanti

Reputation: 575

postgresql: update rows in a selected column with respect to date

I have a table t1 with columns and rows as

date(date)         plan(numeric)    actual(numeric)
2015-01-01               50           36
2015-01-02               60           45
2015-01-03               70           40
2015-01-04               80           36

I want to change rows (only in plan column) with respect to the date. For example i want change rows belongs to 2015-01-01 to 2015-01-30.

expected ouput:

date(date)         plan(numeric)    actual(numeric)
    2014-12-31               45           50
    2015-01-01               50           36
    2015-01-02               50           45
    2015-01-03               50           40
    2015-01-04               50           36
        .
        .
    2015-01-28               50           20

can someone please let me know how can i do these. thank you

Upvotes: 0

Views: 576

Answers (3)

Divya
Divya

Reputation: 137

update table t1 
set plan = 50 
where date >= to_date('2015-01-01' ,'yyyy-MM-dd') 
      and date <= to_date('2015-01-30' ,'yyyy-MM-dd')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

If I understand correctly, you want the first value of the month to be the value for the entire month.

With a query, you can do:

select date,
       first_value(plan) over (partition by extract(year from date), extract(month from date)
                              ) as plan,
       actual
from t1;

Upvotes: 0

Taras Velykyy
Taras Velykyy

Reputation: 1801

UPDATE t1 SET plan = 50 WHERE date >= '2015-01-01' AND date <= '2015-01-30'

Upvotes: 2

Related Questions