- add some UPDATE..FROM tests and also some new reqs

3 files changed, 83 insertions, 4 deletions

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 9681750b2..ae285afa4 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -183,3 +183,24 @@ class SuiteRequirements(Requirements):
         """target database can persist/return an empty string."""
 
         return exclusions.open()
+
+
+    @property
+    def update_from(self):
+        """Target must support UPDATE..FROM syntax"""
+        return exclusions.closed()
+
+    @property
+    def update_where_target_in_subquery(self):
+        """Target must support UPDATE where the same table is present in a
+        subquery in the WHERE clause.
+
+        This is an ANSI-standard syntax that apparently MySQL can't handle,
+        such as:
+
+        UPDATE documents SET flag=1 WHERE documents.title IN
+            (SELECT max(documents.title) AS title
+                FROM documents GROUP BY documents.user_id
+            )
+        """
+        return exclusions.open()
diff --git a/test/orm/test_update_delete.py b/test/orm/test_update_delete.py
index 3d4d2559c..4da07458e 100644
--- a/test/orm/test_update_delete.py
+++ b/test/orm/test_update_delete.py
@@ -1,7 +1,7 @@
 from sqlalchemy.testing import eq_, assert_raises, assert_raises_message
 from sqlalchemy.testing import fixtures
 from sqlalchemy import Integer, String, ForeignKey, or_, and_, exc, \
-    select, func, Boolean
+    select, func, Boolean, case
 from sqlalchemy.orm import mapper, relationship, backref, Session, \
     joinedload, aliased
 from sqlalchemy import testing
@@ -541,7 +541,7 @@ class UpdateDeleteFromTest(fixtures.MappedTest):
               Column('id', Integer, primary_key=True),
               Column('user_id', None, ForeignKey('users.id')),
               Column('title', String(32)),
-              Column('flag', Boolean, default=False)
+              Column('flag', Boolean)
         )
 
     @classmethod
@@ -600,14 +600,56 @@ class UpdateDeleteFromTest(fixtures.MappedTest):
         eq_(
             set(s.query(Document.id, Document.flag)),
             set([
+                    (1, True), (2, None),
+                    (3, None), (4, True),
+                    (5, True), (6, None),
+                ])
+        )
+
+    @testing.requires.update_where_target_in_subquery
+    def test_update_using_in(self):
+        Document = self.classes.Document
+        s = Session()
+
+        subq = s.query(func.max(Document.title).label('title')).\
+                group_by(Document.user_id).subquery()
+
+        s.query(Document).filter(Document.title.in_(subq)).\
+                update({'flag': True}, synchronize_session=False)
+
+        eq_(
+            set(s.query(Document.id, Document.flag)),
+            set([
+                    (1, True), (2, None),
+                    (3, None), (4, True),
+                    (5, True), (6, None),
+                ])
+        )
+
+    @testing.requires.update_where_target_in_subquery
+    @testing.requires.standalone_binds
+    def test_update_using_case(self):
+        Document = self.classes.Document
+        s = Session()
+
+
+        subq = s.query(func.max(Document.title).label('title')).\
+                group_by(Document.user_id).subquery()
+
+        # this would work with Firebird if you do literal_column('1')
+        # instead
+        case_stmt = case([(Document.title.in_(subq), True)], else_=False)
+        s.query(Document).update({'flag': case_stmt}, synchronize_session=False)
+
+        eq_(
+            set(s.query(Document.id, Document.flag)),
+            set([
                     (1, True), (2, False),
                     (3, False), (4, True),
                     (5, True), (6, False),
                 ])
         )
 
-
-
 class ExpressionUpdateTest(fixtures.MappedTest):
     @classmethod
     def define_tables(cls, metadata):
diff --git a/test/requirements.py b/test/requirements.py
index 3e9f5ee0f..ca245eb30 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -184,6 +184,22 @@ class DefaultRequirements(SuiteRequirements):
 
 
     @property
+    def update_where_target_in_subquery(self):
+        """Target must support UPDATE where the same table is present in a
+        subquery in the WHERE clause.
+
+        This is an ANSI-standard syntax that apparently MySQL can't handle,
+        such as:
+
+        UPDATE documents SET flag=1 WHERE documents.title IN
+            (SELECT max(documents.title) AS title
+                FROM documents GROUP BY documents.user_id
+            )
+        """
+        return fails_if('mysql', 'MySQL error 1093 "Cant specify target table '
+                                        'for update in FROM clause"')
+
+    @property
     def savepoints(self):
         """Target database must support savepoints."""