Re: [sqlite] REPLACE INTO Only Executes last SELECT?
So GROUP BY will separate the the grouping done by sum()? So that also means
I could factor in all discounts not just static ones? Like so? Modifier 0 =
Percent Of and 1 = Static amount? Also which statement is going to be more
efficient the UPDATE or the REPLACE?
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance -
SUM((CASE WHEN D.Modifier == 0 THEN (D.Amount / 100.00 * 500) ELSE D.Amount
END))
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
GROUP BY DA.MemberId
Igor Tandetnik wrote:
>
> Cnichols <[EMAIL PROTECTED]> wrote:
>> Thanks alot the UPDATE works great! Although the REPLACE INTO still
>> only executes the last row of a multi discount.
>
> I see. MA to DA is a one-to-many relation, too. You need to move all the
> tables into an inner select: the goal is to use SUM aggregate to produce
> a single row (or no rows) for each MA.MemberId. Like this:
>
> REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT DA.MemberId, MA.Balance - 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
> 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
> GROUP BY DA.MemberId
>
> 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#a7632401
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
So the sum() function brings the multiple discounts together for a member in
the UPDATE statement?
So I have a question regarding this then the Discount.Modifier stands for 0
= percent of a value or 1 = static
The update you constructed would work for all static values since sums
groups all the numerics together.
So I assume if I used sum() for percent of values it would collect the sum
of all values calculated like so for percent of modifier?
sum(D.Amount / 100 * [C#VALUE])
Also would it be logical to have separate Updates (One for Static, One for
Percent of Value) or would it be more logical to try to achieve this by
using ifnull and nullif on the D.Modifier to get the correct sum function()?
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.
>>
>>
>> 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
>>
>>
>> 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#a7631805
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
DOH! I stand corrected. Guess I should RTFM before I speak :-)) Thanks! -Clark - Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, November 30, 2006 4:52:25 PM Subject: Re: [sqlite] REPLACE INTO Only Executes last SELECT? Clark Christensen <[EMAIL PROTECTED]> wrote: > I don't think SQLite supports "REPLACE INTO..." I'm pretty sure "REPLACE" = > is a conflict action in SQLite. > > Perhaps "INSERT OR REPLACE INTO Memb= > erAccounts (MemberId, Balance) SELECT..." will do what you want? > I went to the extra trouble of making REPLACE INTO an alias for INSERT OR REPLACE INTO for compatibility with MySQL. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
Clark Christensen <[EMAIL PROTECTED]> wrote: > I don't think SQLite supports "REPLACE INTO..." I'm pretty sure "REPLACE" = > is a conflict action in SQLite. > > Perhaps "INSERT OR REPLACE INTO Memb= > erAccounts (MemberId, Balance) SELECT..." will do what you want? > I went to the extra trouble of making REPLACE INTO an alias for INSERT OR REPLACE INTO for compatibility with MySQL. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
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.
>>
>>
>> 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
>>
>>
>> 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]
-
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
REPLACE is just an alias for INSERT OR REPLACE.
This SQL Statement works off a Primary key as stated here.
http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSqlUpdateWithFrom
I have an SQL statement that works using this concept.
This SQL Statement will update all accounts for members with an anniversary
betweens dates by 100. Insert will fail because of the Primary Key and the
Replace will occur.
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT M.Id, (MA.Balance + 100)
FROM MemberAccounts AS MA
LEFT JOIN Members AS M ON MA.MemberId = M.Id
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'))
Although for the previous SQL Statement the replace is not occuring for each
row (if multiple discount) only the last row for that member.
Clark Christensen wrote:
>
> I don't think SQLite supports "REPLACE INTO..." I'm pretty sure "REPLACE"
> is a conflict action in SQLite.
>
> Perhaps "INSERT OR REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT..." will do what you want?
>
> -Clark
>
> - Original Message
> From: Cnichols <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Thursday, November 30, 2006 2:22:19 PM
> Subject: [sqlite] REPLACE INTO Only Executes last SELECT?
>
>
> 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.
>
>
> 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
>
>
> 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]
> -
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7630506
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
I don't think SQLite supports "REPLACE INTO..." I'm pretty sure "REPLACE" is a
conflict action in SQLite.
Perhaps "INSERT OR REPLACE INTO MemberAccounts (MemberId, Balance) SELECT..."
will do what you want?
-Clark
- Original Message
From: Cnichols <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 30, 2006 2:22:19 PM
Subject: [sqlite] REPLACE INTO Only Executes last SELECT?
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.
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
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]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
