Re: Update statement problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Zach Wells (no_zwells_spam_at_ain1.com)
Date: 04/26/04 

Date: Mon, 26 Apr 2004 11:50:52 -0400

You can't have the join syntax next to the update like that, you have to
use:

update Partial
set Partial.MfrPaidAmount = Partial.MfrPaidAmount -
BillPayment.AppliedAmount,
Partial.MfrPaidInFull = 0
where Partial right join BillPayment on Partial.PartialID =
BillPayment.PartialID
where BillPayment.PaymentID = 9698

Also note that this is a MSSQL specific solution, this code will not port.

Zach

Vlad wrote:

> I'm rewriting SQL statements used in Access in order to use them with SQL
> Server.
> This SQL works with Access, but says 'incorrect syntax near RIGHT keyword':
>
> UPDATE [Partial] RIGHT JOIN BillPayment ON [Partial].PartialID =
> BillPayment.PartialID SET [Partial].MfrPaidAmount =
> [Partial].MfrPaidAmount - BillPayment.AppliedAmount, [Partial].MfrPaidInFull
> = 0 WHERE BillPayment.PaymentID = 9698
>
> Statement is executed from VB6 app.
> What's wrong with it?
> Thank you
> Vlad
>
>