Posted by Tom Moreau on 02/18/06 18:42

If the new column is always to be the product of two other columns, why not
use a computed column:

alter table MyTable
add
MyCol as (Col1 * Col2)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Dranai" <dranai@gmail.com> wrote in message
news:1140277653.986097.250450@g43g2000cwa.googlegroups.com...
I'm looking into a problem a friend is having, and I'll say right off
the bat that I work with with php and MySQL, and not MS SQL.

What he is attempting to do (in MS SQL) is take two database fields
from a table (string fields), multiply them together, and put them into
a third field. This third column in the table has not yet been created
the time of running the query.

If it needs to be multiple queries, that is fine. My first thought is
to use a simple ALTER query to add the column to the table, then to
call a UPDATE function which uses a select statement inside of it. I'm
not sure if something like this can even be done.

// ------------ Suggested query

UPDATE chrisslu SET 'discquantity' = '(SELECT
chrisslu.quantity*chrisslu.nr_of_disc
FROM chrisslu
WHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE
(str(period,6)>=?Andstr(period,6)<=?)

// ------------ End Suggested query

It starts with an UPDATE, but replaces the value to be set with a
SELECT statement. I honestly don't even think this query is
syntactically correct, I'm just trying to get the general concept down
:).

So, question the first: Is this type of query possible? The reason
I'm doing this is because I was told MS SQL has no way of storing
temporary variables... otherwise I would just call a SELECT statement,
store the variable, and UPDATE the new field from the variable after
the ALTER statement.

Second question: If it is possible, am I on the right track, or does
it need to be entered in completely different than what I have?

Third: Regarding the 'type'. Do I need to do any kind of typecasting
or conversion of the fields? Both chrisslu.quantity and
chrisslu.nr_of_disc are string fields (that is what I was told, they
may be varchar of some kind). In order to use them in a math
statement, do they have to be floats, or doubles, or something similar?

I appreciate any response, I know this was a long winded question.

Chris

[Back to original message]