Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Unanswered: Updating a row with the right connections

    Hi,

    I have a database with two tables:

    Notes
    Appointments

    The Appointments table stores date/time for an appointment as well as the 'member' (or customer) that is attending it.

    The Notes table stores the notes entered after an appointment and is linked to the Appointment table by an 'appointment' field. There is also a 'new' field to show if the Notes have been read or not.

    I need to be able to set the 'new' fields in Notes to 0 for all appointments that relate to, say, member 6.

    I'm sure its a simple SQL process, but I don't know it.

    I tried:

    UPDATE Notes, Appointments SET Notes.new=0 WHERE Appointments.member=6 AND Notes.appointment=Appointments.ref

    But it doesn't like this.

    What is the correct format?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    If your update statement has the correct columns it should read:

    UPDATE Notes
    SET Notes.new=0
    FROM Notes, Appointments
    WHERE Appointments.member=6
    AND Notes.appointment=Appointments.ref

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    Brilliant - thats exactly what I was looking for.

    Never used a 'FROM' in an UPDATE statement before - you learn something new every day.


    Cheers,

    Paul

Posting Permissions

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