Field as view result

Question

Is it possible to "automatically" set the value of a column to the result of a query?

For example I would like to have a column e.g. balance which when I request it, it should return the running total of some transactions but I prefer if that would have been pre-calculated.

Can I somehow "link" a column to the result of a View or a Function?

PS. We use MySQL Cluster

The query looks like the following:

SET @id = 'customerid'; 
SET @accId = 'accid';   

Select  sum(trans.t) - sum(trans.c) as Balance  
from (  select          *, 
        case when ACNO2=@accId then a1 * 1 else a1 * -1 end as t, 
        case when (TrCode= '40' and ACNO2=@accId) then 0 else ChargeAmount1 end as c    
        from OldTransactions    
        where (((CLNO1=@id and CLNO2=@id) or (CLNO1=@id or CLNO2=@id)) and TStatus='OK')) as trans 
where (ACNO1=@accId or ACNO2=@accId)

You can "link" it to some other columns, and have it update whenever those are updated... https://stackoverflow.com/q/5222044 — Siguza, Oct 05 '16 at 18:08
a column value can be a select o using a view a column can be the same the result of a select — ScaisEdge, Oct 05 '16 at 18:13
I have update an answer hope is useful ..otherwise show me a real case so i can help you to find a solution if is possible — ScaisEdge, Oct 05 '16 at 18:19

score 1 · Accepted Answer · answered Oct 05 '16 at 18:20

http://dev.mysql.com/doc/refman/5.7/en/create-table.html says in part:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column.

(emphasis mine)

So you can't set use the DEFAULT syntax to define a column as the result of a subquery.

As an alternative, you could create triggers on the transactions table you want to monitor, so that any change to the transactions updates your balance column.

You should create a trigger for each action: insert, delete, and update.

CREATE TRIGGER trx_ins_balance ON transactions AFTER INSERT
FOR EACH ROW
  UPDATE accounts SET accounts.balance = accounts.balance + NEW.amount
  WHERE accounts.accountid = NEW.accountid;

CREATE TRIGGER trx_del_balance ON transactions AFTER DELETE
FOR EACH ROW
  UPDATE accounts SET accounts.balance = accounts.balance - OLD.amount
  WHERE accounts.accountid = NEW.accountid;

CREATE TRIGGER trx_upd_balance ON transactions AFTER UPDATE
FOR EACH ROW
  UPDATE accounts SET accounts.balance = accounts.balance - OLD.amount + NEW.amount
  WHERE accounts.accountid = NEW.accountid;

The above is just an example. Don't ask me for a more tailored example, because you shouldn't be using code verbatim from Stack Overflow anyway. You should understand how to write your own triggers if you're going to use them.

Another solution is to create a VIEW so that your balance column is actually an expression to read the transactions table. But this won't be pre-calculated. It will re-query the transactions every time you query the view, which might be an expensive solution.

Hm. Updated question with query. Do you think that triggers is a better solution? What if I already have the transactions from an old table (migrated) plus a new table. How will I call the trigger once for the migrated table? — glarkou, Oct 05 '16 at 18:26
Which solution is better depends on how frequently you query the balance, versus how frequently transactions are created. You don't need to call the trigger to initialize the balance, you'd just do a multi-table UPDATE, or perhaps use INSERT...ON DUPLICATE KEY UPDATE — Bill Karwin, Oct 05 '16 at 18:32

Field as view result

1 Answers1