Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Update Query (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: SQL Update Query (2003)

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

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

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Update Query (2003)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: SQL Update Query (2003)

    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

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Update Query (2003)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: SQL Update Query (2003)

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

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Update Query (2003)

    Thanks, I'll try that
    Allison

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •