January 8th, 2014, 04:24 PM
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.
January 14th, 2014, 07:48 AM
Does the subquery itself produce the correct results?