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.
<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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
