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

    Join Date
    Jan 2014
    Rep Power

    Update statement with subquery help

    I have one transaction table that is rolled up into a smaller table. I need to update one value if the other 4 values are similar on each transaction in the smaller table. Larger table is ticketitems, rollup table is rolluptable. ItemUPC is 14 digits(leading zero) in ticketitems table and 13 digits in rollup table.

    UPDATE rolluptable as roll SET price = trans.price FROM
    (SELECT max(price) as price, ts.storenum, ts.ticketdate, ts.qty, right(itemupc, 13) as itemupc
    FROM dbo.ticketitems as ts
    RIGHT JOIN rolluptable as x ON ts.storenum = x.store and ts.ticketdate=x.ticketdate and right(ts.itemupc, 13) = x.itemupc
    group by ts.storeid, ts.ticketdate, ts.qty, ts.itemupc) as trans
    WHERE trans.storenum = roll.store and trans.ticketdate = roll.ticketdate and roll.qty = trans.qty and trans.itemupc = roll.itemupc

    I know I am missing something small, but the update isn't updating the field. Postgresql 9.3 database.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Rep Power
    Does the subquery itself produce the correct results?

IMN logo majestic logo threadwatch logo seochat tools logo