How to dynamically select columns and update them based on variable value?

Question

I have a table which stores the total income on monthly basis. The columns are named as column_1, column_2, ... , column_12. Each month I need to update the value on the column corresponding to the month. For example, Jan = column_1, Feb = column_2 etc. How to dynamically select the column and update the required column every month in a single update statement instead of writing multiple if else in PLSQL? Any help would be appreciated.

Perhaps dynamic SQL is appropriate?

Abra
– Abra

2021-04-22 11:31:58 +00:00
Commented Apr 22, 2021 at 11:31 — Abra
– Abra, Commented Apr 22, 2021 at 11:31

Gordon Linoff · Accepted Answer · 2021-04-22 11:31:49Z

2

Quite painfully. You should probably fix your data model, so each month's data is stored in a row not in a column.

But if you are stuck with someone else's bad data model, you can use:

update t
    set month_1 = (case when extract(month from sysdate) = 1 then :val else month_1 end),
        month_1 = (case when extract(month from sysdate) = 2 then :val else month_2 end),
        . . .   -- repeat for all months
    where <whatever other conditions you have>;

In other words, you are updating all columns. However, 11 of them will get the value they already have.

answered Apr 22, 2021 at 11:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

spartacus Over a year ago

This looks fair enough. No other option, I am stuck with an existing model and cannot make changes to the structure now. Thanks a lot!

Collectives™ on Stack Overflow

How to dynamically select columns and update them based on variable value?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related