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

3 files changed, 47 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 ========================================