Woes using UPDATE with a CTE in MySQL (MariaDB)

Question

I'm going crazy trying to get UPDATE to work with a CTE in MySQL.

Here's a simplified schema of sa_general_journal:

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;

Some records are "split," 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.

When records are imported in bulk, they can't refer to last_insert_ID in order to fill in the Transaction_ID field, thus the need to go clean these up afterward.

This was my first, naive attempt, which said I had an error near UPDATE. Well duh.

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

The CTE itself seems to work, as I can follow it with SELECT * FROM cte and get what I expected.

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. "No problem!" I think, as I code this up:

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

No joy. Same error message.

My understanding is that in MySQL, you don't need a column list, but I did also try this using the column list (a, b, c), with the proper columns referenced in the UPDATE statement, but it still said I had a problem near UPDATE.

There are incredibly few examples of using UPDATE with WITH on the Internet! I found one, from Percona, which I used to create my attempt above, and then found another very similar example from MySQL itself.

Thanks in advance for any help offered!

Answer 1

CTE is a part of subquery definition, not a part of the whole query. The query must be specified after CTE. CTE cannot be used itself. So

UPDATE sa_general_journal gj
JOIN (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)
      SELECT * FROM cte) subquery ON gj.ID = subquery.ID
SET gj.Transaction_ID = subquery.Trans