CODEWITHSUNDEEP
sql-server
bmw0128
bmw0128

Reputation: 13698

Example SQL Server SQL that uses a formula

I have a table that has a field, call it "score". Is it possible to write an SQL that takes the value in each "score" field, inputs the value in a formula, then the output from the formula is written to another field. I have no experience is using formulas in SQL, and I do not know if its possible. Any basic example, if its doable, would be helpful.

Upvotes: 0

Views: 4084

Answers (5)

Lucero
Lucero

Reputation: 60190

Instead of storing it, I'd advise you to compute it in real-time upon query or as computed column or to use an indexed view if performance is too slow.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332541

It's called a computed column - they're supported in:

  • tables
  • SELECT statements
  • VIEWs

Upvotes: 1

recursive
recursive

Reputation: 86064

UPDATE OrderLine SET
TotalPrice = UnitPrice * Quantity

That's it.

Upvotes: 1

Neil N
Neil N

Reputation: 25258

Pretty vague question, but here's the general idea.

UPDATE MyTable SET FormulaOutput = HomeScore + AwayScore

This "formula" just adds the 2 scores together

I suppose you could also calculate the "spread" like this:

UPDATE MyTable SET Spread = MAX(HomeScore,AwayScore) - MIN(HomeScore,AwayScore)

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838086

Yes it's possible, but almost always you don't want to store the calculated field in the database as that would mean your database is not properly normalized.

Instead calculate the value when you query for it by including an extra derived column in your select statement.

An exception to this is if you need to do a time-consuming calculation, in which case you may want to store the derived value in the database. A good way to do this is to use triggers to update value when one of the inputs change, or else run a procedure on a timed interval to update all the derived values in the entire table.

Upvotes: 1

Related Questions