SQLAlchemy: update from_select

Question

I need to execute query like

UPDATE node
SET node.parent_id = node_node.parent_id,
    node.label = node_node.label
FROM node_node
WHERE node_node.child_id = node_id

using SQLAlchemy. I did search the docs, and found only insert().from_select(), but no update().from_select(). I know I can achieve the same programatically, but I need it to be as fast as possible.

Is it possible? Could you give me an example or link to docs/any clue?

Answer 1

Assuming that t_node is node Table instance, while t_node_node - node_node Table instance, see the statement below.

upd = (t_node.update()
        .values(
            parent_id = t_node_node.c.parent_id,
            label = t_node_node.c.label,
            )
        .where(t_node_node.c.child_id == t_node.c.node_id)
        )

Read more on Inserts, Updates and Deletes documentation for more information.