Mysql – How to create a table whose values are based on queries of same table

MySQLtable

This is something that interests me, but I can't seem to find an answer as to how to do it.

Basically, if I have a table and I want a column of that table to be based on a query, particularly when that query is based on information that is inside the same table, how do I do that?

I don't have access to my SQL program right now, but I suspect it would look something like this:

{
     CREATE TABLE ABCD as ABCE
     (

          A int(11) default null,
          B int(11) default sum(ABCE.A)
     );
}

or something to that effect.

Am I way off base?

Best Answer

You are not off-base at all. Actually pretty close. In t-sql you can just use code like this:

CREATE TABLE [dbo].[Sample2](
    [myIntColumn] [int] NULL,
    [myOtherIntColumn] [int] NOT NULL,
    [myBigIntColumn] AS ([myOtherIntColumn]+[myIntColumn])
)

GO

    INSERT INTO Sample2 VALUES (365,256),(10,25000), (1,2)

    SELECT * FROM Sample2

    --myIntColumn   myOtherIntColumn    myBigIntColumn
    --365           256                 621
    --10            25000               25010
    --1             2                   3

If you are looking for an aggregate as the default value, I do not believe you can do that simply. To have an aggregate as a column in the a table, a view is a viable option, like so:

CREATE VIEW Sample3
AS 
WITH CTE AS (
SELECT SUM(myIntColumn) as mySUM
From Sample2 )
SELECT Sample2.*, CTE.mySUM FROM Sample2, CTE
GO

SELECT * FROM Sample3

--myIntColumn   myOtherIntColumn    myBigIntColumn  mySUM
--365           256                 621             376
--10            25000               25010           376
--1             2                   3               376

These queries assume your platform is MS SQL Server.

If your tag was meant to only indicate the language, it would be helpful both for this question and future questions to tag with the platform. For example, you could additionally tag as .

Depending on your RBDMS and your requirements you might be able to leverage an indexed/materialized view. This could additionally help performance, but there are some limitations on an Indexed View in SQL Server.

Related Question