Thanks alot the UPDATE works great! Although the REPLACE INTO still only executes the last row of a multi discount.
Igor Tandetnik wrote: > > Cnichols <[EMAIL PROTECTED]> wrote: >> I have a complex SQL statement which attempts to update all Member >> Accounts balance with a discount if there anniversary falls between >> last handled date and the current day. >> >> <pre> >> REPLACE INTO MemberAccounts (MemberId, Balance) >> >> SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount >> FROM DiscountsApplied AS DA >> LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id >> LEFT JOIN Members AS M ON DA.MemberId = M.Id >> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId >> WHERE >> DATE(strftime('0001-%m-%d', M.Registered)) > >> DATE(strftime('0001-%m-%d', (SELECT RegistrateDate FROM Config))) >> AND >> DATE(strftime('0001-%m-%d', M.Registered)) <= >> DATE(strftime('0001-%m-%d', 'now')) >> AND >> D.Type = 1 AND D.Modifier = 1 >> </pre> >> >> The SELECT statement will return multiple rows. A member may have >> more than one discount that can be applied. When this case occurs >> the last row for that member is the only one that actually executes. >> The rows before that are not replacing the value of BALANCE only the >> last row for that member will affect the balance. > > But of course. You want something like this: > > REPLACE INTO MemberAccounts (MemberId, Balance) > SELECT DA.MemberId, MA.Balance - ifnull( > (SELECT sum(D.Amount) > FROM Discounts AS D, Members AS M > WHERE > DA.DiscountId = D.Id AND > DA.MemberId = M.Id AND > DATE(strftime('0001-%m-%d', M.Registered)) > > DATE(strftime('0001-%m-%d', > (SELECT RegistrateDate FROM Config))) > AND > DATE(strftime('0001-%m-%d', M.Registered)) <= > DATE(strftime('0001-%m-%d', > 'now')) > AND > D.Type = 1 AND D.Modifier = 1 > ), 0) > FROM DiscountsApplied AS DA > LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId > > > Or perhaps > > UPDATE MemberAccounts SET Balance = Balance - ifnull( > (SELECT sum(D.Amount) > FROM DiscountsApplied AS DA > LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id > LEFT JOIN Members AS M ON DA.MemberId = M.Id > WHERE > DA.MemberId = MemberAccounts.MemberId AND > DATE(strftime('0001-%m-%d', M.Registered)) > > DATE(strftime('0001-%m-%d', > (SELECT RegistrateDate FROM Config))) > AND > DATE(strftime('0001-%m-%d', M.Registered)) <= > DATE(strftime('0001-%m-%d', > 'now')) > AND > D.Type = 1 AND D.Modifier = 1 > ), 0) > > Igor Tandetnik > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > -- View this message in context: http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7630798 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------