Search code examples
sqlh2subtraction

How to subtract 2 values from different columns of the same row in the H2 database

I'm trying to subtract 2 values from different columns of the same row in the H2 database.

Database looks like this:

Data types:

SLNO int, NUM1, NUM2 & OUTPUT are long (have entered smaller numbers in this example for easier understanding).

REQUIREMENT:

I'm storing two long numbers under NUM1 & NUM2 columns.

I want to find out the difference between NUM2 - NUM1, the difference has to be stored under the column OUTPUT. I have gone through the documentation H2 Database functions but did not find anything regarding this.

Could you please suggest alternative ways of doing this within the H2 database.

Solution

  • The easiest way is to add calculated column:

    A computed column is a column whose value is calculated before storing. The formula is evaluated when the row is inserted, and re-evaluated every time the row is updated. 

    CREATE TABLE SUBSTRACTION
(
  SLNO INT,
  NUM1 INT,
  NUM2 INT, 
  OUTPUT INT AS (NUM2 - NUM1)
);

    This will guarantee that OUTPUT will be always up-to-date.