title: Trigger updating multiple records in the same table in MySQL
body: <p>I have a simple table:</p>
  
  <pre><code>CREATE  TABLE `accounting`.`People` (
    `ID` INT NOT NULL AUTO_INCREMENT ,
    `Name` VARCHAR(45) NULL ,
    `Property_number` VARCHAR(45) NULL ,
    `People_at_Location` INT NULL ,
    PRIMARY KEY (`ID`) );
  
  INSERT INTO `accounting`.`People` (`Name`, `Property_number`, `People_at_Location`) VALUES ('Jim', '13', '2');
  INSERT INTO `accounting`.`People` (`Name`, `Property_number`) VALUES ('Tony', '16');
  INSERT INTO `accounting`.`People` (`Name`, `Property_number`) VALUES ('Alice', '9');
  INSERT INTO `accounting`.`People` (`Name`, `Property_number`, `People_at_Location`) VALUES ('Martha', '13', '2');
  INSERT INTO `accounting`.`People` (`Name`, `Property_number`) VALUES ('Vandy', '');
  </code></pre>
  
  <p>In our data we know the name for each and every row/record.  But when we started we did not have Property_number.    As we receive an email back from our clients we get their Property_number, and we update the record.   </p>
  
  <p>What we really need is a Trigger that looks at the Property_number and queries to see how many other records in the database have the same property number and updates all the records as we now know an additional person is at that Property_number.</p>
  
  <p>For example  (given the sample data above) it look like:</p>
  
  <pre><code>ID     Name     Property_number     People_at_location
  1      Jim      13                  2
  2      Tony     16                  Null
  3      Alice    9                   1
  4      Martha   13                  2
  5      Vandy    Null                Null      
  </code></pre>
  
  <p>So we get new information from Vandy that tells us she is in property_number 13.  We want to update record 1, 4 and 5 to reflect the updated People_at_location count.</p>
  
  <pre><code>ID     Name     Property_number     People_at_location
  1      Jim      13                  3
  2      Tony     16                  Null
  3      Alice    9                   1
  4      Martha   13                  3
  5      Vandy    13                  3      
  </code></pre>
  
  <p>What would this trigger look like?</p>