Support for multi-table updates with limits, offsets and orders

4 files changed, 77 insertions, 2 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
index 7543d35d3b..83e64d3c43 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
@@ -312,6 +312,9 @@ module ActiveRecord
       def join_to_update(update, select) #:nodoc:
         subselect = select.clone
         subselect.ast.cores.last.projections = [update.ast.key]
+
+        update.ast.limit  = nil
+        update.ast.orders = []
         update.wheres = [update.ast.key.in(subselect)]
       end
 
diff --git a/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb
index c01a64e354..172d08b6f4 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb
@@ -577,8 +577,29 @@ module ActiveRecord
         where_sql
       end
 
+      # In the simple case, MySQL allows us to place JOINs directly into the UPDATE
+      # query. However, this does not allow for LIMIT, OFFSET and ORDER. To support
+      # these, we must use a subquery. However, MySQL is too stupid to create a
+      # temporary table for this automatically, so we have to give it some prompting
+      # in the form of a subsubquery. Ugh!
       def join_to_update(update, select) #:nodoc:
-        update.table select.ast.cores.last.source
+        if select.limit || select.offset || select.orders.any?
+          subsubselect = select.ast.clone
+          subsubselect.cores.last.projections = [update.ast.key]
+          subsubselect = Arel::Nodes::TableAlias.new(
+            Arel::Nodes::Grouping.new(subsubselect),
+            '__active_record_temp'
+          )
+
+          subselect = Arel::SelectManager.new(select.engine, subsubselect)
+          subselect.project(Arel::Table.new('__active_record_temp')[update.ast.key.name])
+
+          update.ast.limit  = nil
+          update.ast.orders = []
+          update.wheres = [update.ast.key.in(subselect)]
+        else
+          update.table select.ast.cores.last.source
+        end
       end
 
       protected
diff --git a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
index ea0970028c..bd6cb2d3b8 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
@@ -491,8 +491,29 @@ module ActiveRecord
         execute("RELEASE SAVEPOINT #{current_savepoint_name}")
       end
 
+      # In the simple case, MySQL allows us to place JOINs directly into the UPDATE
+      # query. However, this does not allow for LIMIT, OFFSET and ORDER. To support
+      # these, we must use a subquery. However, MySQL is too stupid to create a
+      # temporary table for this automatically, so we have to give it some prompting
+      # in the form of a subsubquery. Ugh!
       def join_to_update(update, select) #:nodoc:
-        update.table select.ast.cores.last.source
+        if select.limit || select.offset || select.orders.any?
+          subsubselect = select.ast.clone
+          subsubselect.cores.last.projections = [update.ast.key]
+          subsubselect = Arel::Nodes::TableAlias.new(
+            Arel::Nodes::Grouping.new(subsubselect),
+            '__active_record_temp'
+          )
+
+          subselect = Arel::SelectManager.new(select.engine, subsubselect)
+          subselect.project(Arel::Table.new('__active_record_temp')[update.ast.key.name])
+
+          update.ast.limit  = nil
+          update.ast.orders = []
+          update.wheres = [update.ast.key.in(subselect)]
+        else
+          update.table select.ast.cores.last.source
+        end
       end
 
       # SCHEMA STATEMENTS ========================================
diff --git a/activerecord/test/cases/relations_test.rb b/activerecord/test/cases/relations_test.rb
index 7bd9c44651..97abd67385 100644
--- a/activerecord/test/cases/relations_test.rb
+++ b/activerecord/test/cases/relations_test.rb
@@ -973,4 +973,34 @@ class RelationTest < ActiveRecord::TestCase
     assert_equal count, comments.update_all(:post_id => posts(:thinking).id)
     assert_equal posts(:thinking), comments(:greetings).post
   end
+
+  def test_update_all_with_joins_and_limit
+    comments = Comment.joins(:post).where('posts.id' => posts(:welcome).id).limit(1)
+    assert_equal 1, comments.update_all(:post_id => posts(:thinking).id)
+  end
+
+  def test_update_all_with_joins_and_limit_and_order
+    comments = Comment.joins(:post).where('posts.id' => posts(:welcome).id).order('comments.id').limit(1)
+    assert_equal 1, comments.update_all(:post_id => posts(:thinking).id)
+    assert_equal posts(:thinking), comments(:greetings).post
+    assert_equal posts(:welcome),  comments(:more_greetings).post
+  end
+
+  def test_update_all_with_joins_and_offset
+    all_comments = Comment.joins(:post).where('posts.id' => posts(:welcome).id)
+    count        = all_comments.count
+    comments     = all_comments.offset(1)
+
+    assert_equal count - 1, comments.update_all(:post_id => posts(:thinking).id)
+  end
+
+  def test_update_all_with_joins_and_offset_and_order
+    all_comments = Comment.joins(:post).where('posts.id' => posts(:welcome).id).order('posts.id')
+    count        = all_comments.count
+    comments     = all_comments.offset(1)
+
+    assert_equal count - 1, comments.update_all(:post_id => posts(:thinking).id)
+    assert_equal posts(:thinking), comments(:more_greetings).post
+    assert_equal posts(:welcome),  comments(:greetings).post
+  end
 end