CODEWITHSUNDEEP
sqlpostgresql
Matt
Matt

Reputation: 1521

Retrieve values in same row of SQL table

I have a table that I use to calculate prices that looks up a table to get price per part and then multiplies that by number of parts ordered.

This number of parts ordered is in the same table however, and I can't seem to find a way to access values in the same row in a table when adding values.

Is this not possible, or are there better ways of doing this?

Here is the table this concerns:

CREATE TABLE PartOrder 
(
    OrderID     INTEGER NOT NULL,
    CustomerID  INTEGER NOT NULL,
    PartID          INTEGER NOT NULL, 
    NumParts    INTEGER NOT NULL,
    Status      CHAR(1) NOT NULL 
                CHECK (Status IN ('R', 'H',
                'E', 'C')
    OrderTime TIMESTAMP NOT NULL,
    TotalCost   DECIMAL,
    CONSTRAINT partOrder_pk PRIMARY KEY (OrderID),
    CONSTRAINT partOrder_fk1 FOREIGN KEY (CustomerID) REFERENCES Customer ON DELETE CASCADE,
    CONSTRAINT partOrder_fk2 FOREIGN KEY (FlightID) REFERENCES Part ON DELETE CASCADE
);

I want it so that it will take the numParts value and multiply it by the price per part referenced in the parts table. however, I can't stick to hard values as the number ordered may change later, meaning that the totalPrice will change.

At the moment my insert statement is just:

INSERT INTO PartOrder VALUES (001, 001, 001, 4, 'R', NOW(), (4*(SELECT PricePerPart FROM Part WHERE PartID = 001)));

Upvotes: 0

Views: 81

Answers (1)

A Hocevar
A Hocevar

Reputation: 726

You might want to give a look at SELECT INTO as well http://www.postgresql.org/docs/8.1/static/sql-selectinto.html

Or You can go with

INSERT INTO PartOrder
    SELECT (1,1,1,4,'R',NOW, (4*Part.PricePerPart)) 
    FROM Part 
    WHERE Part.PartId = 1;

Upvotes: 3

Related Questions