UPDATE on INNER JOIN

I have an RSVP form for a wedding. I have 2 tables (main and guest). Table main has the main guest, table guest has the associated guest included in that party. I am able to pull them using INNER JOIN, but am unable to UPDATE them all with the same submit.

My tables are set up like this:

table main:

id | firstName | lastName | meal | rsvp

table guest:

id | guestFirstName | guestLastName | guestMeal | guestRsvp | mainID

My UPDATE is written like this:

$$updateGuests = "UPDATE guest.id 
                     SET guestMeal = '$guestMeal', 
                         guestNotes = '$guestNotes', 
                         guestAccept = '$guestAccept' 
                   WHERE guest.mainID = '$id'";
mysql_query($updateGuests);

Some parties will be more than 2 (main guest, + 1-3 associated guests from the guest table. I am trying to update all of their info with one 'su开发者_StackOverflowbmit'

---

From the MySQL forums: Re: UPDATE multiple tables with one UPDATE statement
From the MySQL Reference: 12.2.10. UPDATE Syntax

I would assume your PHP code to be something on the lines of

$updateGuests = 
    "UPDATE 
         main M LEFT JOIN guest G ON M.id = G.mainID
     SET 
         G.guestMeal = '$guestMeal', 
         G.guestNotes = '$guestNotes', 
         G.guestAccept = '$guestAccept',
         M.meal = '$something'
     WHERE G.mainID = '$id'";

The above, of course, will not work for rows that do not actually exist on the guest table - it's an UPDATE after all. Assuming you DO have a way to link specific guests and their data from the submitted data (maybe their id or the guest table?) you can perform selective updates through a loop and then append the CASE statements to the query to update all of the registered guests at once:

$sql = 
    "UPDATE 
        main M LEFT JOIN guests G ON M.id = G.mainID
    SET
        M.meal = '$something'
        ";

for ($i=0; $i < count($yourArrayHere); $i++) { 
    /* ... code to extract values from array here ...*/
    $sql .= 
    ", G.guestMeal = CASE G.id WHEN '$guestID' THEN '$guestMeal' END
    ";
}

$sql .= "WHERE M.id = '$id'";

The above is written based on the assumption that you do need to update the main table with some data. Since it's logically a single row everytime, it's shown at the top of the query. The piece on the loop is static - so you'd only add your fields like that if you always have a strict set of populated fields in your submit data. If that's not true, you might want to make it slightly more flexible by adding a way to translate form field names into database field names and then generically modifying the string to append to the $base sql statement on the target field and on the source var - variable variables ($$var) are one way to achieve this. You'd then leave the CASE statements based on G.id untouched (of course, also assuming that one is the key field you'd also forcedly have available on your data for uniquely identifying the child table's rows).

Of course you'd also need to extract the variables you want on the for loop, to then use them as I've used on the code above

Easier done than said.

---

If I understand your question, you want to update all of the guest records where they match the main.ID. You should be able to do this without an inner join, as follows:

UPDATE guest   
  SET guestMeal ='$guestMeal', guestNotes = '$guestNotes', 
      guestAccept = '$guestAccept' 
  WHERE guest.mainID ='$id'

This will update all of the guest records who are linked to your main record ID.

---

The general update query syntax (for what you are trying to do) is like

UPDATE [table-name]
   SET field-name1 = value1,
   SET field-name2 = value2,
   SET field-name3 = value3
   WHERE some-condition

The update query you have is just a little off.