Thread: Mysql

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    1
    Rep Power
    0

    Mysql


    I want to update table Products with respect to the another table Products_Purchased. If I add new item to Products_Purchased, it must be entered in table Products too and also should update TotalQuantity field of table Products . Whats wrong in this query as it gives null to totalQuantity field ?and doesn't update table if new entry is entered in Products_Puchased table.

    update products as a,products_purchased as b set a.total_quantity=

    case

    when a.date_of_lastupdate < b.PurchaseDate

    then (select * from (select sum(products.total_quantity)+sum(products_purchased.quantity) from products as a natural join products_purchased as b)as c)

    else a.total_quantity

    end

    where a.productname=b.productname ;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    your subquery, the one with the sums, obtains the grand total of all purchases in both tables -- surely that's not what you want to update each row with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo