Results 1 to 3 of 3

Thread: update table

  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: update table

    Hi again. I have these tables :

    buy with

    CODE_client,code_book and values,(has more columns,not important ones)
    cl01, b02
    cl01, b07
    cl02, b01
    cl02, b02
    cl02, b03
    cl02, b06
    cl02, b07
    cl02, b08
    cl03, b01
    cl04, b06

    and BOOK with

    code_book,price and values,(has more columns,not important ones)
    b01, 120
    b02, 60
    b03, 70
    b06, 80
    b07, 90
    b08, 90

    and CLIENT with

    code_client,cleio and values,(has more columns,not important ones)
    cl01, 50
    cl02, 50
    cl03, 50
    cl04, 50

    and i must ADD 1 UNIT to the cleio column, for every 10 euros every client has spent for each book he bought.

    eg
    cl02 bought 6 books so i have to make this in CLIENT.CLEIO

    (cl02)56

    I tried something like this but it doesn't work:

    UPDATE clients
    SET clients.cleio=clients.cleio+tmp.Total/10
    FROM CLIENT
    JOIN (SELECT buy.code_client,SUM(book.price) as Total
    FROM BUY
    join bobk
    ON book.code_book=buy.code_book
    GROUP BY buy.code_client)tmp
    ON tmp.code_Client=client.code_client

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There is no "UPDATE ... FROM" in Oracle. Try this:
    Code:
    UPDATE clients
    SET clients.cleio=clients.cleio+(SELECT NVL(SUM(book.price),0) as Total
                                     FROM BUY
                                     JOIN book
                                     ON book.code_book=buy.code_book
                                     WHERE buy.code_Client=client.code_client
                                    );
    The NVL is there to prevent cleio being set to NULL for clients who didn't buy a book.

  3. #3
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by andrewst
    There is no "UPDATE ... FROM" in Oracle. Try this:
    Code:
    UPDATE clients
    SET clients.cleio=clients.cleio+(SELECT NVL(SUM(book.price),0) as Total
                                     FROM BUY
                                     JOIN book
                                     ON book.code_book=buy.code_book
                                     WHERE buy.code_Client=client.code_client
                                    );
    The NVL is there to prevent cleio being set to NULL for clients who didn't buy a book.
    Thanks works just fine. Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •