SQL Update Query (2003)

[Allison]

I have another probelm; The following query,

UPDATE WSH18Week.dbo.TblAppointmentsWithPPI
SET WSH18Week.dbo.TblAppointmentsWithPPI.PPI = WSH18Week.dbo.TblAppointmentsWithPPI.CRN +
RIGHT(YEAR(WSH18Week.dbo.TblAppointmentsWithPPI.Re fDate),2) +
month(WSH18Week.dbo.TblAppointmentsWithPPI.RefDate +
day(WSH18Week.dbo.TblAppointmentsWithPPI.RefDate) + WSH18Week.dbo.TblSpecialties.f_specedpc,
WSH18Week.dbo.TblAppointmentsWithPPI.PPIIssuer = 'RGR'
FROM WSH18Week.dbo.TblAppointmentsWithPPI
INNER JOIN WSH18Week.dbo.TblSpecialties
ON WSH18Week.dbo.TblAppointmentsWithPPI.SPEC = WSH18Week.dbo.TblSpecialties.F_LSPEC
INNER JOIN WSH18Week.dbo.TblClinicCodes
ON WSH18Week.dbo.TblAppointmentsWithPPI.CLINIC = WSH18Week.dbo.TblClinicCodes.CODE
WHERE WSH18Week.dbo.TblAppointmentsWithPPI.PPI Is Null

gives the error message

Line 6: Incorrect syntax near '='.

Again, you help would be very much appreciated.
Thanks
Allison

[HansV]

You cannot have a FROM clause in an UPDATE statement. The syntax is

UPDATE ...
SET ...
WHERE ...

[Allison]

Having taken out the 'from' statement, I still get the same error
Allison

[HansV]

Does this work?

UPDATE WSH18Week.dbo.TblAppointmentsWithPPI
INNER JOIN WSH18Week.dbo.TblSpecialties
ON WSH18Week.dbo.TblAppointmentsWithPPI.SPEC = WSH18Week.dbo.TblSpecialties.F_LSPEC
INNER JOIN WSH18Week.dbo.TblClinicCodes
ON WSH18Week.dbo.TblAppointmentsWithPPI.CLINIC = WSH18Week.dbo.TblClinicCodes.CODE
SET WSH18Week.dbo.TblAppointmentsWithPPI.PPI = WSH18Week.dbo.TblAppointmentsWithPPI.CRN +
Right(YEAR(WSH18Week.dbo.TblAppointmentsWithPPI.Re fDate),2) +
Month(WSH18Week.dbo.TblAppointmentsWithPPI.RefDate +
Day(WSH18Week.dbo.TblAppointmentsWithPPI.RefDate) + WSH18Week.dbo.TblSpecialties.f_specedpc,
WSH18Week.dbo.TblAppointmentsWithPPI.PPIIssuer = 'RGR'
WHERE WSH18Week.dbo.TblAppointmentsWithPPI.PPI Is Null

[Allison]

No, now it reckons that there is;
Incorrect syntax near the keyword 'INNER'
on Line 2

[HansV]

This is becoming too complicated. Create the query in design view, and look at the SQL when you've got it working correctly.

[Allison]

Thanks, I'll try that
Allison