SQLAlchemy多列相关更新？

Question

这个问题在stackoverflow上已经被问了多次，但是他们都超过一年了，因此我想再次提出，以防有更新。

相关更新是指一个更新语句，它基于另一个表中的值来更新一个表中的所有行，同时将这两个表联系在一起。

从SQLAlchemy文档可以看出，我们可以轻松地进行相关更新，但只能在单个列上进行:

update(foo).values(bar=select([foobar.c.bar]).where(foobar.c.id == foo.c.id))

这将转化为：

UPDATE foo
SET bar = (
    SELECT bar
    FROM foobar
    WHERE foobar.id = foo.id
) 

我们如何在SQLAlchemy中编写一个使用多列的相关更新呢？例如：

UPDATE foo
SET (bar, baz) = (
    SELECT bar, baz
    FROM foobar
    WHERE foobar.id = foo.id
) 

- Matthew Moisen

Answer 1

根据您的头像和描述，我猜测您正在使用Oracle。从这个答案中可以得出以下SQLAlchemy组合，如果您的连接结果是保留键视图：

stmt = select([foo.c.bar.label('foo_bar'),
               foo.c.baz.label('foo_baz'),
               foobar.c.bar.label('foobar_bar'),
               foobar.c.baz.label('foobar_baz')]).\
    where(foo.c.id == foobar.c.id)

update(stmt).values({stmt.c.foo_bar: stmt.c.foobar_bar,
                     stmt.c.foo_baz: stmt.c.foobar_baz})

这会产生以下SQL语句：

UPDATE (SELECT foo.bar AS foo_bar,
               foo.baz AS foo_baz,
               foobar.bar AS foobar_bar,
               foobar.baz AS foobar_baz
        FROM foo, foobar
        WHERE foo.id = foobar.id)
SET foo_bar=foobar_bar, foo_baz=foobar_baz

标签很重要，因为您的表共享列名。

您还可以生成原始目标SQL：

from sqlalchemy import tuple_, select, exists

stmt = select([foobar.c.bar, foobar.c.baz]).where(foo.c.id == foobar.c.id)
foo.update().\
    values({tuple_(foo.c.bar, foo.c.baz).self_group(): stmt}).\
    where(exists(stmt))

self_group()方法非常重要，因为编译器似乎会忽略元组周围的括号，在这种情况下会产生错误的语法。我添加了WHERE子句以避免更新没有匹配的foobar行中的foo行：

UPDATE foo SET (bar, baz)=(SELECT foobar.bar, foobar.baz 
FROM foobar 
WHERE foo.id = foobar.id) WHERE EXISTS (SELECT foobar.bar, foobar.baz 
FROM foobar 
WHERE foo.id = foobar.id)

- Ilja Everilä

---

你是个英雄，谢谢！我想请你在每一个你能找到的“具有多列相关更新”的问题上都发布这个答案！ - Matthew Moisen

你的第一种方法和第二种方法相比，有什么优势吗？ - Matthew Moisen

我猜第一种方法不需要单独进行EXISTS测试以避免在第二种方法中更新不匹配的行是一个优点。虽然第一种方法可能更好地支持复杂的连接视图，但它受到键保留的限制。在某些情况下，这可能会迫使您选择第二种方法。我在Oracle方面几乎没有经验，所以我将把详细信息留给读者自己去发现。 - Ilja Everilä