title: MySQL update updating more rows than it should be
body: <p>I'm having issues where an update statement should (to my knowledge) update 5 rows (I have selected 5 rows into a temp table and used an INNER JOIN in the update statement)</p>
  
  <p>however when it comes to running the update statement it updates anything that could have been selected into the temp table not just the joined contents of the temp table itself.</p>
  
  <p>I'm using the <code>FOR UPDATE</code> code in the selection statement to lock the rows, (as I'm expecting multiple queries to be aimed at this table at one time, (NOTE removing that does not change the error effect)</p>
  
  <p>I've generalised the entire code base and it still has the same effect, I've been on this for the past few days and I'm sure that its just something silly I must be doing</p>
  
  <p><strong>Code description</strong></p>
  
  <p><code>TABLE `data`.`data_table</code> <em>Table to store data and to show it has been taken my the external program.</em></p>
  
  <p><code>Stored Procedure `admin`.`func_fill_table</code> <em>debug code to populate the above table.</em></p>
  
  <p><code>Stored Procedure `data`.`func_get_data</code> <em>Actual code designed to retrieve a batch size records, mark them as picked up and then return them to an external application.</em></p>
  
  <p><strong>Basic Setup Code</strong></p>
  
  <pre><code>DROP TABLE IF EXISTS `data`.`data_table`;
  DROP PROCEDURE IF EXISTS `admin`.`func_fill_table`;
  DROP PROCEDURE IF EXISTS `data`.`func_get_data`;
  DROP SCHEMA IF EXISTS `data`;
  DROP SCHEMA IF EXISTS `admin`;
  CREATE SCHEMA `admin`;
  CREATE SCHEMA `data`;
  
  CREATE TABLE `data`.`data_table` (
    `identification_field_1` char(36) NOT NULL,
    `identification_field_2` char(36) NOT NULL,
    `identification_field_3` int(11) NOT NULL,
    `information_field_1` int(11) NOT NULL,
    `utc_action_time` datetime NOT NULL,
    `utc_actioned_time` datetime DEFAULT NULL,
    PRIMARY KEY (`identification_field_1`,`identification_field_2`,`identification_field_3`),
    KEY `NC_IDX_data_table_action_time` (`utc_action_time`)
  );
  </code></pre>
  
  <p><strong>Procedure Creation</strong></p>
  
  <pre><code>DELIMITER //
  
  CREATE PROCEDURE `admin`.`func_fill_table`(
      IN records int
  )
  BEGIN
      IF records &lt; 1
          THEN SET records = 50;
      END IF;
  
      SET @processed = 0;
      SET @action_time = NULL;
  
      WHILE @processed &lt; records
      DO
          SET @action_time = DATE_ADD(now(), INTERVAL FLOOR(RAND()*(45)-10) MINUTE);#time shorter for temp testing
          SET @if_1 = UUID();
          SET @if_2 = UUID();
          INSERT INTO data.data_table(
              identification_field_1
              ,identification_field_2
              ,identification_field_3
              ,information_field_1
              ,utc_action_time
              ,utc_actioned_time)
          VALUES (
               @if_1
              ,@if_2
              ,FLOOR(RAND()*5000+1)
              ,FLOOR(RAND()*5000+1)
              ,@action_time
              ,NULL);
  
          SET @processed = @processed +1;
      END WHILE;
  END
  //
  CREATE PROCEDURE `data`.`func_get_data`(
      IN batch int
  )
  BEGIN
      IF batch &lt; 1
          THEN SET batch = 1; /*Minimum Batch Size of 1 */
      END IF;
  
      DROP TABLE IF EXISTS `data_set`;
      CREATE TEMPORARY TABLE `data_set` 
          SELECT 
              `identification_field_1` as `identification_field_1_local`
              ,`identification_field_2` as `identification_field_2_local`
              ,`identification_field_3` as `identification_field_3_local`
          FROM `data`.`data_table`
          LIMIT 0; /* Create a temp table using the same data format as the table but insert no data*/
  
      SET SESSION sql_select_limit = batch;
  
      INSERT INTO `data_set` (
          `identification_field_1_local`
          ,`identification_field_2_local` 
          ,`identification_field_3_local`)
      SELECT 
          `identification_field_1`
          ,`identification_field_2` 
          ,`identification_field_3` 
       FROM `data`.`data_table`
       WHERE
          `utc_actioned_time` IS NULL
              AND `utc_action_time` &lt; NOW()
      FOR UPDATE; #Select out the rows to process (up to batch size (eg 5)) and lock those rows
  
      UPDATE 
         `data`.`data_table` `dt`
      INNER JOIN 
          `data_set` `ds`
          ON (`ds`.`identification_field_1_local` = `dt`.`identification_field_1`
              AND `ds`.`identification_field_2_local` = `dt`.`identification_field_2`
              AND `ds`.`identification_field_3_local` = `dt`. `identification_field_3`)
      SET `dt`.`utc_actioned_time` = NOW();
      # Update the table to say these rows are being processed
  
      select ROW_COUNT(),batch;
      #Debug output for rows altered (should be maxed by batch number)
  
      SELECT * FROM 
          `data`.`data_table` `dt`
      INNER JOIN 
          `data_set` `ds`
          ON (`ds`.`identification_field_1_local` = `dt`.`identification_field_1`
              AND `ds`.`identification_field_2_local` = `dt`.`identification_field_2`
              AND `ds`.`identification_field_3_local` = `dt`. `identification_field_3`);
      # Debug output of the rows that should have been modified
  
      SELECT 
         `identification_field_1_local`
          ,`identification_field_2_local` 
          ,`identification_field_3_local`
      FROM 
          `data_set`; /* Output data to external system*/
  
      /* Commit the in process field and allow other processes to access thoese rows again */
  END;
  //
  </code></pre>
  
  <p><strong>Run Code</strong></p>
  
  <pre><code>call `admin`.`func_fill_table`(5000);
  call `data`.`func_get_data`(5);
  </code></pre>