syntax error with mysql bulk update via INSERT ... SELECT ... ON DUPLICATE KEY UPDATE

[CaselIT]

Discussed in #10514

^{Originally posted by anentropic October 20, 2023}
I am trying to bulk update via an insert on duplicate, but using from_select (instead of list of values as seen here #9328)

I am getting error from mysql like:

(MySQLdb.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near
'AS new ON DUPLICATE KEY UPDATE dealer_dealer_name = new.dealer_dealer_name, cust' at line 3")
[SQL: INSERT INTO mytable (...) SELECT dealer_dealer_names.value AS dealer_dealer_name, ...
FROM mytable
LEFT OUTER JOIN etl_anonymised_company_name AS dealer_dealer_names ON md5(mytable.dealer_dealer_name) = dealer_dealer_names.`key`
...<more similar joins>...
WHERE mytable.last_modified_date >= %s AND mytable.last_modified_date < %s
ORDER BY mytable.last_modified_date
AS new
ON DUPLICATE KEY UPDATE dealer_dealer_name = new.dealer_dealer_name, ...]

my sqlalchemy looks like:

    insert_clause = insert(MyTable).from_select(
        [col.name for col in insert_columns],
        make_select_query(insert_columns),
    )
    update_query = (
        insert_clause.on_duplicate_key_update({
            col_name: getattr(insert_clause.inserted, col_name)
            for col_name in update_columns
        })
    )
    with engine.begin() as conn:
        result = conn.execute(update_query)

mysql error seems to imply it doesn't like how sqlalchemy has aliased the select query AS new (it's not something I have done explicitly in my select instance)

if I print str(update_query) it looks different, there's no select alias and the field updates look like ON DUPLICATE KEY UPDATE dealer_dealer_name = VALUES(dealer_dealer_name)

I realise this isn't a minimal reproducible case at the moment, just wondered if there's something obvious I should be doing differently