def test_update_pulls_from_cte(self):
products = table("products", column("id"), column("price"))
cte = products.select().cte("pd")
assert "autocommit" not in cte.select()._execution_options
stmt = products.update().where(products.c.price == cte.c.price)
eq_(stmt.compile().execution_options["autocommit"], True)
self.assert_compile(
stmt,
"WITH pd AS "
"(SELECT products.id AS id, products.price AS price "
"FROM products) "
"UPDATE products SET id=:id, price=:price FROM pd "
"WHERE products.price = pd.price",
)
eq_(stmt.compile().isupdate, True)
def test_update_against_cte_directly(self):
"""test #6464
for UPDATE, I'm not sure this is a valid syntax on any platform.
"""
products = table("products", column("id"), column("price"))
cte = products.select().cte("pd")
assert "autocommit" not in cte.select()._execution_options
stmt = update(cte)
eq_(stmt.compile().execution_options["autocommit"], True)
self.assert_compile(
stmt,
"WITH pd AS (SELECT products.id AS id, products.price AS price "
"FROM products) UPDATE pd SET id=:id, price=:price",
)
eq_(stmt.compile().isupdate, True)
def test_delete_against_cte_directly(self):
"""test #6464.
SQL-Server specific arrangement seems to allow
DELETE from a CTE directly.
"""
products = table("products", column("id"), column("price"))
cte = products.select().cte("pd")
assert "autocommit" not in cte.select()._execution_options
stmt = delete(cte)
eq_(stmt.compile().execution_options["autocommit"], True)
self.assert_compile(
stmt,
"WITH pd AS (SELECT products.id AS id, products.price AS price "
"FROM products) DELETE FROM pd",
)
eq_(stmt.compile().isdelete, True)