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]
-----------------------------------------------------------------------------
