Insert calculated column into a table-CodePudding

I have been trying to do something in SQL and I cannot quite find the right answer on Stack Overflow. I have a table (called RMBS_STANDARDIZED) already created and it has a column LOAN_PERC_ISSUER. The table is the union of several other tables where I standardized naming convention.

What I want to do after loading the table is to update it with this inner query (noting when I run this it returns the correct values are returned but obviously as a single column). Therefore, I want to store the returned query in the table.

SELECT 
    ISSUER_SERIES,
    SUM(COALESCE(LOAN_BALANCE, 0)) AS TOTAL_BALANCE 
FROM 
    RMBS_STANDARDIZED 
GROUP BY 
    ISSUER_SERIES

The insert query I am trying to use is

INSERT INTO RMBS_STANDARDIZED (LOAN_PERC_ISSUER)
    SELECT
        COALESCE(T1.LOAN_BALANCE, 0) / T2.TOTAL_BALANCE
    FROM 
        RMBS_STANDARDIZED T1
    LEFT JOIN
        (SELECT 
             ISSUER_SERIES,
             SUM(COALESCE(LOAN_BALANCE, 0)) AS TOTAL_BALANCE 
         FROM 
             RMBS_STANDARDIZED 
         GROUP BY 
             ISSUER_SERIES) T2 ON T1.ISSUER_SERIES = T2.ISSUER_SERIES

But I get the following error

Cannot insert the value NULL into column 'ISSUER'

If I adjust the ISSUER column to allow NULLs, then the column does not update or remains NULL.

Alternatively, I have tried the following

ALTER TABLE RMBS_STANDARDIZED  
    ADD New_Column AS (SELECT
                           COALESCE(T1.LOAN_BALANCE, 0) /  T2.TOTAL_BALANCE
                       FROM 
                           RMBS_STANDARDIZED T1
                       LEFT JOIN
                           (SELECT 
                                ISSUER_SERIES,
                                SUM(COALESCE(LOAN_BALANCE, 0)) AS TOTAL_BALANCE 
                            FROM 
                                RMBS_STANDARDIZED GROUP BY ISSUER_SERIES) T2
                           ON T1.ISSUER_SERIES = T2.ISSUER_SERIES)

But get the following error

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Any ideas would be helpful. I am better off using a CTE or creating a temp/hash table before creating the final table? I am using SQL Server and SQL Server Management Studio.

CodePudding user response：

Your first query fail because LOAN_PERC_ISSUER has a NOT NULL constraint. Rewrite your query as :

SELECT COALESCE(T1.LOAN_BALANCE / T2.TOTAL_BALANCE, 0)
FROM   RMBS_STANDARDIZED AS T1
       LEFT OUTER JOIN (SELECT ISSUER_SERIES, 
                               SUM(COALESCE(LOAN_BALANCE, 0)) AS TOTAL_BALANCE
                        FROM   RMBS_STANDARDIZED
                        GROUP  BY ISSUER_SERIES) AS T2 
           ON T1.ISSUER_SERIES = T2.ISSUER_SERIES;

A computed column cannot have subquery of anykind and must use only data comming from the row's values. There is a possibility to put the query into an UDF and use the UDF for the computed column, but it is not well recommended...

A much proper way is to use a trigger