How to get data from a calculated column in a select used in an insert statement-CodePudding

I have one table like this. In this table, Balance Column is calculated from data of another table when insert, and Rate is calculated from Balance.

Id   Name   Balance   Rate
1     AAA    1000     200

Balance: from data of another table. Rate: Balance x 0.2

INSERT INTO TableA (Name, Balance, Rate)
SELECT
   'AAA' AS Name,
   ((Score   100) * 3) - 20 as Balance
   Balance * 0.2 as Rate    <--- Instead of (((Score   100) * 3) - 20) *0.2 
FROM TableB where PKey = 1

My question is, since Balance is calculated when insert, it seems that I’m not able to get the value for Rate on the same SELECT command? (If not repeat the same calculation of Balance on Rate Column)
Is it possible to use one command to insert complete data?
Thank you.

CodePudding user response：

You can query on the result of another query, look at this example

select t.Name,
       t.Balance,
       t.Balance * 0.2 as Rate
from   ( SELECT 'AAA' AS Name,
                ((Score   100) * 3) - 20 as Balance
         FROM   TableB 
         where  PKey = 1
       ) as t

For large queries and complicated calculations this can make the query more readable, there should be no affect on the performance

Look at this DBFiddle to see how it works