CODEWITHSUNDEEP
sqloracle-databasesubtraction
Nikita Pronin
Nikita Pronin

Reputation: 89

Subtracting a value in a column based on condition using PLSQL

A simple thing to do I believe.
Say I have a table:

CREATE TABLE kpi
( KPI_ID number(10) NOT NULL,
  KPI_NAME varchar2(50) NOT NULL,
  DAY_DATE DATE
);

And I know thet someone made a mistake and added 100 to all values higher than 100 when the table was created.
I need a query where 100 is subtracted from all values in KPI_ID column that are >100.
And I only have access to a read mode

Upvotes: 1

Views: 1532

Answers (4)

Nikita Pronin
Nikita Pronin

Reputation: 89

In the end all that was needed is a CASE statement:
CASE WHEN KPI_ID > 100 then (KPI_ID -100) ELSE KPI_ID END AS KPI_ID

Upvotes: 0

APC
APC

Reputation: 146199

someone made a mistake and added 100 to all values higher than 100 when the table was created ... I need values ​​that are less than 100 to remain intact, and those that are greater than or equal to 100 should be reduced by 100

You need to conditionally apply an adjustment to the KPI_ID. For this we use case() statements:

select case when (kpi_id - 100) >= 100 then 
         kpi_id - 100 
       else
         kpi_id 
       end as kpi_id
       , kpi_name
       , day_date
from kpi
;

In your original question you said "all values in KPI_ID column that are >100" but in your latest comment you say "greater than or equal to 100". My amended solution implements the algorithm from your comment, on the assumption that the most recently stated requirement is always right.

Upvotes: 2

Md. Masud Iqbal
Md. Masud Iqbal

Reputation: 113

"a query where 100 is subtracted from all values in KPI_ID column that are >100"

SELECT KPI_ID-100 as KPI_ID,KPI_NAME,DAY_DATE  FROM kpi WHERE KPI_ID >100

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Use update. The only number that you have is kpi_id, so I assume that is the column you are referring to:

update kpi
    set kpi_id = kpi_id - 100
    where kpi_id > 100;

If you just want a query:

select kpi_id - 100 as kpi_id, kpi_name, day_date
from kpi;

Upvotes: 1

Related Questions