CODEWITHSUNDEEP
sqlsql-server
Iatrochemist
Iatrochemist

Reputation: 276

computed column using other table information

I have two tables in my database as follows : 

create table materialPriceAndStandard (
  id int identity(700,3),
  materialName nvarchar(100) not null default (0), 
  unitPrice decimal(19,2) not null default (0),
  carbohydrate tinyint not null default (0),
  protein tinyint not null default (0),
  fat tinyint not null default (0),
  humidity tinyint not null default (0) ,
  minerals tinyint not null default (0),
  totalPrice decimal(19,2) not null
  default((select _weight from ingredients where material = materialName)*(unitPrice)),
  constraint PK_id_materialPriceAndStandard primary key (id),
  constraint UQ_materialName_materialPriceAndStandard unique (materialName), 
  constraint CHECK_totlaMineralAmount check (carbohydrate + protein + fat + humidity + minerals  =100 )
)

and the second one: 

create table ingredients(
  _index int identity(1,1),
   id int,material nvarchar(100),     
   _weight float, _percent float,
   constraint PK_id_material_ingredients primary key (id,material),
   constraint FK_id_ingredients foreign key (id) references productType (id)
)

I want to calculate the amount of 'totalPrice' column in materialPriceAndStandar table using a sub query which uses the _weight value in 'ingredients' table, but I get this error :

Msg 1046, Level 15, State 1, Line 10
Subqueries are not allowed in this context. Only scalar expressions are allowed.

What is the best method to compute the 'totalPrice' column's value as computed column using the _weight column in the ingredients table ?

Upvotes: 0

Views: 51

Answers (1)

Tanner
Tanner

Reputation: 22753

You can't build a subquery in to a table creation script.

You will have to do it in 2 steps:

  1. Create the table
  2. Update the values using your query once the table is created either using a trigger or write it in to the INSERT/UPDATE script

Upvotes: 1

Related Questions