<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">One mysql table can be updated by the results of another table but the<br>
mysql UPDATE statement requires all tables referenced<br>
viz:<br>
UPDATE T1,T2 SET T1.col = T2.col WHERE T1.id = T2.id<br>
and not (as sqla currently does)<br>
UDPDATE T1 SET T1.col = T2.col WHERE T1.id = T2.id<br>
which produces an error</p>
<p dir="auto">The attached python code uses the excellent sqla compiler to<br>
enhance the Update compiler but the changes are so trivial I think<br>
they should go into dialects/mysql/base.py</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1944/update.patch">update.patch</a> | <a href="../wiki/imported_issue_attachments/1944/enhance2.py">enhance2.py</a> | <a href="../wiki/imported_issue_attachments/1944/enhance.py">enhance.py</a> | <a href="../wiki/imported_issue_attachments/1944/base.py.patch">base.py.patch</a></p>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">When calling the <code class="notranslate">visit_on_duplicate_key_update</code> method, the compiled output will replace all the <code class="notranslate">insert_stmt.inserted</code> aliases to the one matching the <code class="notranslate">column.name</code>, even if the alias points to another object.</p>
<p dir="auto">The issue could be considered a split-off from <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="571864941" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/5173" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/5173/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/5173">#5173</a>, at <a href="https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_3_24/lib/sqlalchemy/dialects/mysql/base.py#L1436">https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_3_24/lib/sqlalchemy/dialects/mysql/base.py#L1436</a>, where it sets <code class="notranslate">"VALUES(" + self.preparer.quote(column.name) + ")"</code>.</p>
<p dir="auto">I think the best way is to use <code class="notranslate">obj.name</code> instead of <code class="notranslate">column.name</code>.</p>
<p dir="auto">Btw, the issue can be reproduced on both <code class="notranslate">1.3.24</code> and <code class="notranslate">1.4.26</code>.</p>
<h3 dir="auto">To Reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from datetime import datetime
from sqlalchemy import Column, Integer, String, Boolean, DateTime, func
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class DebugTbl(Base):
    __tablename__ = 'debug_tbl'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    enabled = Column(Boolean)
    updated = Column(DateTime)


entries = [
    {
        'name': 'first',
        'enabled': True,
        'updated': datetime.utcnow(),
    },
    {
        'name': 'second',
        'enabled': False,
        'updated': datetime.utcnow(),
    },
]

# check the insert/update against the `debug_tbl` table, with IF statement

insert_stmt = mysql.insert(DebugTbl).values(entries)
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
    name=func.IF(insert_stmt.inserted.updated &gt; DebugTbl.updated,
                 insert_stmt.inserted.name,
                 DebugTbl.name),
    enabled=func.IF(insert_stmt.inserted.updated &gt; DebugTbl.updated,
                    insert_stmt.inserted.enabled,
                    DebugTbl.enabled),
    updated=func.IF(insert_stmt.inserted.updated &gt; DebugTbl.updated,
                    insert_stmt.inserted.updated,
                    DebugTbl.updated),
)
sql_statement = on_duplicate_key_stmt.compile(dialect=mysql.dialect())

assert sql_statement.string == (
    'INSERT INTO debug_tbl (name, enabled, updated) '
    'VALUES (%s, %s, %s), (%s, %s, %s) '
    'ON DUPLICATE KEY UPDATE '
    'name = IF(VALUES(updated) &gt; debug_tbl.updated, VALUES(name), debug_tbl.name), '
    'enabled = IF(VALUES(updated) &gt; debug_tbl.updated, VALUES(enabled), debug_tbl.enabled), '
    'updated = IF(VALUES(updated) &gt; debug_tbl.updated, VALUES(updated), debug_tbl.updated)'
)"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">datetime</span> <span class="pl-k">import</span> <span class="pl-s1">datetime</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-v">String</span>, <span class="pl-v">Boolean</span>, <span class="pl-v">DateTime</span>, <span class="pl-s1">func</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>


<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()


<span class="pl-k">class</span> <span class="pl-v">DebugTbl</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'debug_tbl'</span>

    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>)
    <span class="pl-s1">enabled</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Boolean</span>)
    <span class="pl-s1">updated</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">DateTime</span>)


<span class="pl-s1">entries</span> <span class="pl-c1">=</span> [
    {
        <span class="pl-s">'name'</span>: <span class="pl-s">'first'</span>,
        <span class="pl-s">'enabled'</span>: <span class="pl-c1">True</span>,
        <span class="pl-s">'updated'</span>: <span class="pl-s1">datetime</span>.<span class="pl-en">utcnow</span>(),
    },
    {
        <span class="pl-s">'name'</span>: <span class="pl-s">'second'</span>,
        <span class="pl-s">'enabled'</span>: <span class="pl-c1">False</span>,
        <span class="pl-s">'updated'</span>: <span class="pl-s1">datetime</span>.<span class="pl-en">utcnow</span>(),
    },
]

<span class="pl-c"># check the insert/update against the `debug_tbl` table, with IF statement</span>

<span class="pl-s1">insert_stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">mysql</span>.<span class="pl-en">insert</span>(<span class="pl-v">DebugTbl</span>).<span class="pl-en">values</span>(<span class="pl-s1">entries</span>)
<span class="pl-s1">on_duplicate_key_stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">insert_stmt</span>.<span class="pl-en">on_duplicate_key_update</span>(
    <span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s1">func</span>.<span class="pl-v">IF</span>(<span class="pl-s1">insert_stmt</span>.<span class="pl-s1">inserted</span>.<span class="pl-s1">updated</span> <span class="pl-c1">&gt;</span> <span class="pl-v">DebugTbl</span>.<span class="pl-s1">updated</span>,
                 <span class="pl-s1">insert_stmt</span>.<span class="pl-s1">inserted</span>.<span class="pl-s1">name</span>,
                 <span class="pl-v">DebugTbl</span>.<span class="pl-s1">name</span>),
    <span class="pl-s1">enabled</span><span class="pl-c1">=</span><span class="pl-s1">func</span>.<span class="pl-v">IF</span>(<span class="pl-s1">insert_stmt</span>.<span class="pl-s1">inserted</span>.<span class="pl-s1">updated</span> <span class="pl-c1">&gt;</span> <span class="pl-v">DebugTbl</span>.<span class="pl-s1">updated</span>,
                    <span class="pl-s1">insert_stmt</span>.<span class="pl-s1">inserted</span>.<span class="pl-s1">enabled</span>,
                    <span class="pl-v">DebugTbl</span>.<span class="pl-s1">enabled</span>),
    <span class="pl-s1">updated</span><span class="pl-c1">=</span><span class="pl-s1">func</span>.<span class="pl-v">IF</span>(<span class="pl-s1">insert_stmt</span>.<span class="pl-s1">inserted</span>.<span class="pl-s1">updated</span> <span class="pl-c1">&gt;</span> <span class="pl-v">DebugTbl</span>.<span class="pl-s1">updated</span>,
                    <span class="pl-s1">insert_stmt</span>.<span class="pl-s1">inserted</span>.<span class="pl-s1">updated</span>,
                    <span class="pl-v">DebugTbl</span>.<span class="pl-s1">updated</span>),
)
<span class="pl-s1">sql_statement</span> <span class="pl-c1">=</span> <span class="pl-s1">on_duplicate_key_stmt</span>.<span class="pl-en">compile</span>(<span class="pl-s1">dialect</span><span class="pl-c1">=</span><span class="pl-s1">mysql</span>.<span class="pl-en">dialect</span>())

<span class="pl-k">assert</span> <span class="pl-s1">sql_statement</span>.<span class="pl-s1">string</span> <span class="pl-c1">==</span> (
    <span class="pl-s">'INSERT INTO debug_tbl (name, enabled, updated) '</span>
    <span class="pl-s">'VALUES (%s, %s, %s), (%s, %s, %s) '</span>
    <span class="pl-s">'ON DUPLICATE KEY UPDATE '</span>
    <span class="pl-s">'name = IF(VALUES(updated) &gt; debug_tbl.updated, VALUES(name), debug_tbl.name), '</span>
    <span class="pl-s">'enabled = IF(VALUES(updated) &gt; debug_tbl.updated, VALUES(enabled), debug_tbl.enabled), '</span>
    <span class="pl-s">'updated = IF(VALUES(updated) &gt; debug_tbl.updated, VALUES(updated), debug_tbl.updated)'</span>
)</pre></div>
<h3 dir="auto">Error</h3>
<p dir="auto">AssertionError</p>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: Windows</li>
<li>Python: 3.8.8</li>
<li>SQLAlchemy: 1.3.24</li>
<li>Database: MySQL 5.7.33</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): pymysql 0.9.3</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto"><em>No response</em></p>