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