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.
Is there a way I can accumalate the discounts for a member and apply it to
their balance with an SQL statement? Or will this have to be done
programmtically with SELECT and then nested in a transaction UPDATES?
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7629298
Sent from the SQLite mailing list archive at Nabble.com.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
