Re: Embedded Queries?

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@xxxxxxxxx> wrote in message
news:1140277653.986097.250450@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Embedded Queries?
    ... What he is attempting to do (in MS SQL) is take two database fields ... the time of running the query. ... to use a simple ALTER query to add the column to the table, ... UPDATE chrisslu SET 'discquantity' = '(SELECT ...
    (comp.databases.ms-sqlserver)
  • Re: query Access table structures
    ... Access does not expose the data structure via a SQL statement, ... You can certainly execute DDL query statements such as: ... ALTER TABLE MyTable ADD COLUMN MyNewTextField TEXT; ...
    (microsoft.public.access.queries)
  • Re: Data Definition Query
    ... Below is the code I typed in a pass through query. ... that what I am doing will not alter or change the SQL tables in any way as I ... ADD CONSTRAINT ...
    (microsoft.public.access.queries)
  • query Access table structures
    ... If I can use SQL to ALTER a tables structure, how can I query the currect ... Oracle has a describe command ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)