Anyone Here Know Sql?

[Sparkle1984]

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,
CategoryID INTEGER,
PublisherID INTEGER,
       CONSTRAINT PrimaryKey PRIMARY KEY
       (
         BookID
       )
);

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.
SQLSTATE=42703


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. ;)

[shn]

google. :lol:

[bulio]

or a linux help forum (found on google) :P

[Sparkle1984]

OK, I've found my way to http://www.linuxquestions.org/ forum and posted my problem on there.
I hope someone there will know, as this has to be in tomorrow. Although it doesn't matter too much - this is only one small part of the task, and I've done nearly everything else. :)

[4play]

have you declared discount somewhere and have you called it in the from statement.


im doing sql at the minute so im still learning.