I am having a problem with an SQL query. This is my original table definition in IBM DB2:
CREATE TABLE Book
BookID INTEGER NOT NULL,
Title VARCHAR(50) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY
That works fine, but now I want to write a query which will make a discount on all the books which have a certain CategoryID.
For example, implement a discount of 10% on all books in categoryID 1:
CREATE VIEW DiscountView AS SELECT BookID, Title, Price, CategoryID, PubliserID, Price/10 AS Discount FROM Book WHERE Book.CategoryID = 1;
SELECT * FROM DiscountView WHERE Book.CategoryID = 1;
UPDATE Book SET Price = Price - Discount WHERE Book.CategoryID = 1;
The Update statement is where it all goes wrong - I get an error message saying:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "DISCOUNT" is not valid in the context where it is used.
Does anyone know how I can get it to work? It has to take a percentage value, because later I will write a Java Database Connectivity program whereby a user will specify a percentage value, to discount all the books in a specific category.
Thanks for any help.