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?

van · Accepted Answer · 2014-04-12 16:28:38Z

14

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.

answered Apr 12, 2014 at 16:28

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bartolo-otrit Over a year ago

In particular: "The WHERE clause can refer to multiple tables. For databases which support this, an UPDATE FROM clause will be generated"

Collectives™ on Stack Overflow

SQLAlchemy: update from_select

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related