Re: Inserting new rows that use divided values

From: Mary Bray (no_at_spam.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 20:13:33 +1000

do it with an insert ... select eg:

INSERT INTO dbo.order_cost (cost_each, cost_tax, cost_ship)
select cost_each / total_cost, cost_tax / total_cost, cost_ship / total_cost
from dbo.order_cost
where ship_code = 1

With an insert statement you use the Values key word if you have variables
or exact values, but use Select to insert rows from a query. 

-- 
------
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"bfwcom" <bfwcom@discussions.microsoft.com> wrote in message 
news:35650B1C-D11D-48FD-96ED-7E7FB5722DB6@microsoft.com...
>I am having a problem with syntax on an INSERT INTO statement. I may be 
>using
> the wrong statement to perform the task, but am not an expert and am still
> trying to learn the right way to write SQL. I have created a table and 
> added
> it to an existing database. Now I need to perform some work.
>
> First my table:
>
> CREATE TABLE dbo.order_cost
>                     (ship_code integer IDENTITY(1,1) NOT NULL,
>                     cost_each numeric(2, 2) NOT NULL,
>                     cost_tax numeric(2, 2) NOT NULL ,
>                     cost_ship numeric(2, 2) NOT NULL,
>                     total_cost numeric(3, 2) NULL,
> PRIMARY KEY (ship_code))
> GO
>
> Next I create a relationship with another table (order_lines) in the
> database and insert data into the table:
>
> ALTER TABLE order_lines ADD ship_code integer,
> CONSTRAINT orderlines2ordercost
> FOREIGN KEY (ship_code)
> REFERENCES order_cost;
> GO
>
> INSERT INTO dbo.order_cost (ship_code, cost_each, cost_tax, cost_ship)
> VALUES (1, 15.95, 1.89, 12.75);
> GO
>
> Then I put a value in the last column based on the sum of 3 other fields:
>
> UPDATE order_cost
> SET total_cost = (cost_each + cost_tax + cost_ship)
> WHERE ship_code = 1;
>
> Here's where I am having a problem- I need to insert another row that
> divides the values in each of the fields from row one by the value of the
> last field in row one:
>
> INSERT INTO dbo.order_cost (cost_each, cost_tax, cost_ship)
> VALUES (cost_each / total_cost, cost_tax / total_cost, cost_ship / 
> total_cost)
> GO
>
> I get this error:
>
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'cost_each' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
>
> I know it can be done, but cannot find any reference to help me with the
> correct syntax. this should be easy, but it has me banging my head.

Relevant Pages

  • Re: information_Schema.Routines
    ... SQL Server MVP ... I need to find all references to 'atable' in all SPs in my database. ...
    (microsoft.public.sqlserver.programming)
  • Re: DB Architecture Questions (for joe celko)
    ... So a deck of punch cards or a mag tape is just like an SQL Schema to ... a table has a name in the database. ... OCCURS clause. ... This rule would be enforce by a REFERENCES clause on the Orders table ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Design Question
    ... > requires more than two probes, no matter how large the database. ... > acceptable (in the relational model) to have an Identity attribute to ... the gap in the sequence is not filled in and the sequence ... > vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Mixed up with Relationships..help!
    ... The database you're describing ... The CONSTRAINT statement establishes a Primary Key or a Foreign Key, ... "REFERENCES" table and column. ... QuestionaireAnswers: QuestionaireAnswersID, StudentID, 2005, ...
    (microsoft.public.access.gettingstarted)
  • Re: One PC not able to calculate formulas
    ... Are you using ONE database on a server that everyone is accessing? ... If you get no errors then select Tools: References ... Then check the reference libraries on that computer. ... If any of the selected references have "MISSING:" in front of them, unselect ...
    (microsoft.public.access.gettingstarted)
Quantcast