title: Woes using UPDATE with a CTE in MySQL (MariaDB)
body: <p>I'm going crazy trying to get UPDATE to work with a CTE in MySQL.</p>
  <p>Here's a simplified schema of <code>sa_general_journal</code>:</p>
  <pre><code>CREATE TABLE `sa_general_journal` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `Transaction_ID` int(10) unsigned DEFAULT NULL COMMENT 'NULL if not split, same as ID for split records',
    `Date` timestamp NOT NULL DEFAULT current_timestamp(),
  …
    `Statement_s` int(10) unsigned DEFAULT NULL,
  …
    `Name` varchar(255) DEFAULT NULL,
  …
    PRIMARY KEY (`ID`),
  …
  ) ENGINE=InnoDB AUTO_INCREMENT=25929 DEFAULT CHARSET=utf8;
  </code></pre>
  <p>Some records are &quot;split,&quot; for example, a credit card statement amount might have a sales tax amount that is split out. In such cases, both parts of the split record have the same ID in the Transaction_ID field.</p>
  <p>When records are imported in bulk, they can't refer to <code>last_insert_ID</code> in order to fill in the Transaction_ID field, thus the need to go clean these up afterward.</p>
  <p>This was my first, naive attempt, which said I had an error near <code>UPDATE</code>. Well duh.</p>
  <pre><code>WITH cte AS (
      SELECT
          ID,
          MIN(ID) OVER(PARTITION BY `Date`, `Name`, Statement_s) AS Trans,
          Transaction_ID
      FROM sa_general_journal
      WHERE Transaction_ID = 0)
  UPDATE cte
  SET Transaction_ID = Trans
  </code></pre>
  <p>The CTE itself seems to work, as I can follow it with <code>SELECT * FROM cte</code> and get what I expected.</p>
  <p>So I started searching StackOverflow, and discovered that CTEs are not updatable, but that you need to join them to what you want to update. &quot;No problem!&quot; I think, as I code this up:</p>
  <pre><code>WITH cte AS (
      SELECT
          ID,
          MIN(ID) OVER(PARTITION BY `Date`, `Name`, Statement_s) AS Trans,
          Transaction_ID
      FROM sa_general_journal
      WHERE Transaction_ID = 0)
  UPDATE sa_general_journal gj, cte
  SET gj.Transaction_ID = cte.Trans
  WHERE gj.ID = cte.ID
  </code></pre>
  <p>No joy. Same error message.</p>
  <p>My understanding is that in MySQL, you don't need a column list, but I did also try this using the column list <code>(a, b, c)</code>, with the proper columns referenced in the <code>UPDATE</code> statement, but it still said I had a problem near <code>UPDATE</code>.</p>
  <p>There are incredibly few examples of using <code>UPDATE</code> with <code>WITH</code> on the Internet! I found one, <a href="https://www.percona.com/blog/2020/02/10/introduction-to-mysql-8-0-common-table-expressions-part-1/" rel="nofollow noreferrer">from Percona</a>, which I used to create my attempt above, and then found another very similar example <a href="https://bugs.mysql.com/bug.php?id=88397" rel="nofollow noreferrer">from MySQL itself</a>.</p>
  <p>Thanks in advance for any help offered!</p>