title: How can one break up an UPDATE into subqueries?
body: <p>There exists a table:</p>
  
  <pre><code>CREATE TABLE person
  (
      id INT(10) PRIMARY KEY AUTO_INCREMENT,
      nameFirst VARCHAR(255) DEFAULT '?',
      nameSecond VARCHAR(255) DEFAULT '',
      fatherNameFirst VARCHAR(255) DEFAULT NULL
  );
  </code></pre>
  
  <p>Note: There are actually other columns there, 18 in total, but they are not being used here.</p>
  
  <p>The goal is to set up father's first name from using second name of the child. It can be predicted from the second name (patronymic) in russian language, but not always correctly. So i plan to do what can be done automatically and some will do by hand later.</p>
  
  <p>So the <code>UPDATE</code> done as follows:</p>
  
  <pre><code>UPDATE person AS child
  LEFT JOIN
    (SELECT DISTINCT nameFirst FROM person) AS parent
      ON CONCAT(parent.nameFirst,'овна')=child.nameSecond OR CONCAT(parent.nameFirst,'ович')=child.nameSecond
  SET child.fatherNameFirst=parent.nameFirst;
  </code></pre>
  
  <p>Eventually it will need to run on a table that has >2m entries, for now i have tried with the sample data of 400k. The problem is that after about an hour of my computer using one if its cores at 100% the query has not yet finished.</p>
  
  <p>So i was thinking if i can break it up into subqueries, so these can be set to run one after another, but they should each take 5-10 minutes. This way if i need to do something, i can terminate currently running one and not lose a day of CPU time.</p>
  
  <p>I have attempted to add: <code>WHERE child.id&lt;1000</code> but either it was still way too long or had little impact (perhaps i misunderstand how MariaDB opens up this update).</p>
  
  <p>In case sample data will actually help somebody understand it better:</p>
  
  <pre><code>select id, nameFirst, nameSecond from person limit 10;
  +----+--------------------+----------------------------+
  | id | nameFirst          | nameSecond                 |
  +----+--------------------+----------------------------+
  |  1 | Туликович          |                            |
  |  2 | Август             | Михайлович                 |
  |  3 | Август             | Христианович               |
  |  4 | Александр          | Александрович              |
  |  5 | Александр          | Христьянович               |
  |  6 | Альберт            | Викторович                 |
  |  7 | Альбрехт           | Александрович              |
  |  8 | Амалия             | Андреевна                  |
  |  9 | Амалия             | Ивановна                   |
  | 10 | Ангелина           | Андреевна                  |
  +----+--------------------+----------------------------+
  </code></pre>
  
  <p><code>fatherNameFirst</code> is empty at this time.</p>