具有多列的 SQLAlchemy 相关更新? [英] SQLAlchemy Correlated Update with Multiple Columns?
问题描述
这个问题已经在 stackoverflow 上被问过多次,但他们似乎都已经超过一年了,所以我想我会再问一次,以防万一有更新.
This question has been asked multiple times on stackoverflow, but they all appeared to be over a year old so I figured I would ask again in case there has been an update.
correlated update
是一种更新语句,它根据另一个表中的值更新一个表中的所有行,同时将两个表链接在一起.
A correlated update
is an update statement that updates all rows in one table based on values from another table, while linking the two tables together.
从 SQLAlchemy 文档,我们可以轻松进行相关更新,但仅限于单列:
From the SQLAlchemy docs, we can do a correlated update easily but only on a single column:
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 中使用多个列编写相关更新?例如:
How can we write a correlated update using more than one column in sqlalchemy? For example:
UPDATE foo
SET (bar, baz) = (
SELECT bar, baz
FROM foobar
WHERE foobar.id = foo.id
)
推荐答案
根据您的头像和描述,我猜您正在使用 Oracle.来自这个答案,如果您的加入导致 关键保留视图:
Based on your avatar and description I'm guessing you're using Oracle. From this answer one can device the following SQLAlchemy concotion, if your join results in a key preserved view:
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
标签很重要,因为您的表共享列名.
The labels are important since your tables share column names.
您还可以生成原始目标 SQL:
You can also produce your original target 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 子句以避免更新 foo 没有匹配的 foobar 行:
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)
这篇关于具有多列的 SQLAlchemy 相关更新?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!