0

I have two tables: The first one contains numeral values, it looks like this:

The id column is the primary key; it has the attribute AUTO_INCREMENT. All columns have the datatype INT.

Now my question: Can I create a table like the following: 

create table testsumtable
(
    id int primary key auto_increment,
    sumColumn int generated always as (sum(SELECT intone, inttwo, intthree FROM valuestable WHERE id=new.id))
);
GMB
  • 216,147
  • 25
  • 84
  • 135
STh
  • 746
  • 9
  • 24
  • I'm sorry for the typing mistake: It is intthree with two and not three 'e'. – STh Mar 16 '20 at 17:48
  • Are you just looking to create a generated column that contains the sum of the three other columns? – GMB Mar 16 '20 at 17:57

1 Answers1

1

Are you just looking to create a generated column that contains the sum of the three other columns?

If so, you should add it directly to the original table:

alter table mytable
add column sumColumn as (intone + inttwo + intthree)

If you don't want (or can't) alter the original table, I would suggest creating a view instead:

create view myview as
select id, intone, inttwo, intthree, intone + inttwo + intthree sumColumn from mytable
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Thank you for your answer! I'll just create a sumColumn as you suggested and make the sumColumn from the sumTable as a foreign key pointing to the sumColumn from the first table. – STh Mar 17 '20 at 07:16