update_all behavior is different between mysql and the other databases

[such]

I'm trying to copy one column from one table to another. It does not work with Postgresql.

I'm not sure if this is a bug or if this works by luck in MySQL but here is my case:

I execute

ModelA.join(:modelB).update_all('modelA.column = modelB.column')

That fails with:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "modelBs"
LINE 1: ..." SET modelAs.column = modelBs...
                                                         ^
UPDATE "modelAs" SET modelAs.column = modelBs.column WHERE "modelAs"."id" IN (
  SELECT "modelAs"."id" FROM "modelAs" INNER JOIN "modelBs" ON "modelBs"."id" = "modelAs"."modelB_id"
)

It succeeds with MySQL, the following SQL query being generated:

UPDATE "modelAs" INNER JOIN "modelBs" ON "modelBs"."id" = "modelAs"."modelB_id" SET modelAs.column = modelBs.column

Thanks to AbstractMysqlAdapter redefining join_for_update from the DatabaseStatements module.

For this to work with Postgresql, we would need to generate:

UPDATE "modelAs" SET modelAs.column = modelBs.column FROM ModelAs, ModelBs WHERE modelBs"."id" = "modelAs"."modelB_id"

[robin850]

Hello,

Thanks for reporting but could you provide an executable gist to showcase the problem please ?

[such]

Here it is: https://gist.github.com/such/8136555

Changing the adapter to mysql2 allows the test to pass (even though it doesn't demonstrate that the update is actually done then... but it is !)

[robin850]

(Thanks for the gist ; as far as I can see, it fails as well on SQLite)

[maxrossello]

So, what is an equivalent syntax we can use instead?

[rafaelfranca]

This issue has been automatically marked as stale because it has not been commented on for at least three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master, please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

[robin850]

This is still a problem on 4-1-stable and current master.

[joseluistorres]

Actually the UPDATE with JOIN in PostgreSQL will be like this:

http://www.postgresqltutorial.com/postgresql-update/#crayon-5394fa6c719bb531006798

UPDATE posts
 SET title = authors.name 
FROM authors
WHERE
 posts.author_id = authors.id;

working on the fix... cc @robin850 @such

[joseluistorres]

Ok it also fails with SQLite

  1) Error:
RelationTest#test_update_all_with_joins:
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: authors.name: 
UPDATE "posts" SET title = authors.name 
WHERE "posts"."id" IN (SELECT "posts"."id" FROM "posts" 
INNER JOIN "authors" ON "authors"."id" = "posts"."author_id")

and something like this will fix it:

UPDATE "posts" SET title = (SELECT authors.name 
FROM "authors" where "authors"."id" = "posts"."author_id") 
WHERE EXISTS (
SELECT authors.name FROM "authors" where "authors"."id" = "posts"."author_id")

[rails-bot]

This issue has been automatically marked as stale because it has not been commented on for at least three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master, please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

[stormsilver]

I can confirm this is still happening on master as of today: https://gist.github.com/stormsilver/6245b43838e77f5c6636

[Fire-Dragon-DoL]

I'm quite sure I stumbled upon the same bug probably. I'm using Rails 4.1.4, ruby 2.2.1 and Postgresql 9.4.1

Steps

class User < ActiveRecord::Base
  belongs_to :group
end

class Group < ActiveRecord::Base
  has_many :users
end

Code wrote directly in rails console:

user_group = Group.where(name: 'User').first

User.eager_load(:group).references(:groups).where("groups.name = 'Contractor' OR groups.name = 'ContractorManager'").update_all(group_id: user_group.id)

This update_all statement translates into this query:

UPDATE "users" SET "group_id" = 5 WHERE "users"."id" IN (SELECT "users"."id" FROM "users"  WHERE "users"."enabled" = 't' AND (groups.name = 'Contractor' OR groups.name = 'ContractorManager')  ORDER BY email asc)

Which is wrong because the nested query is missing the join. When not using update_all I get:

SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, "groups"."id" AS t1_r0, "groups"."name" AS t1_r1 FROM "users" LEFT OUTER JOIN "groups" ON "groups"."id" = "users"."group_id" WHERE "users"."enabled" = 't' AND (groups.name = 'Contractor' OR groups.name = 'ContractorManager')  ORDER BY email asc

Which is correct. I expect the code to work in both cases, it can obviously be worked around.

[vnavkal]

+1, failing with PostgreSQL 9.3.5, Rails 4.1.5, and Ruby 2.2.2.

[Soleone]

I just ran into a similar issue. I am only working with MySQL here (slightly related #522):

If I understand correctly update_all using joins only works without using a limit or offset with the mysql adapter.

Once you add a limit or offset the underlying query that gets generated for mysql uses an anonymous subquery and then you can no longer reference a column from the joined table.

Trying to update columns in table A with columns from table B. The following works fine:

> CardInteraction.joins(:card).update_all("card_interactions.handle = cards.handle")

UPDATE card_interactions
INNER JOIN cards ON cards.id = card_interactions.card_id
SET card_interactions.handle = cards.handle

=> 7

Now as soon as you introduce a limit or offset the query generator no longer uses a JOIN statement but instead a subquery:

Mysql2::Error: Unknown column 'cards.handle' in 'field list':

> CardInteraction.joins(:card).offset(1).update_all("card_interactions.handle = cards.handle")

UPDATE card_interactions
SET card_interactions.handle = cards.handle
WHERE card_interactions.id IN (
  SELECT id FROM (
    SELECT  card_interactions.id
    FROM card_interactions
    INNER JOIN cards ON cards.id = card_interactions.card_id
    WHERE LIMIT 1 OFFSET 1
  )
__active_record_temp)

The main issue is not necessarily that it creates subqueries but that the subqueries are anonymous and as such I no longer have access to cards.handle to pass into the update_all part.

[joseluistorres]

@Soleone I would suggest you use plain SQL there, I investigated this myself to see why it fails and it goes back to Arel, not a clear solution actually :flushed:

[Soleone]

@joseluistorres okay thanks! for now i will probably do plain SQL.

unfortunately in the framework i'm using right now we expect relations to be passed around (e.g. CardInteraction.joins(:card) to automatically append limit and offset clauses to iterate through millions of rows on several sharded databases). would be cool if eventually activerelation would be able to support this, but not sure how monumental that task is.

[geralddzx]

I am running into the same issue. Is this fixed on any of the new versions? I'm considering importing my database to mysql to get around this.

[CodingAnarchy]

Ran into this problem in Postgres on Rails 5.2 and Ruby 2.5.3 now. Is there any chance we can get this addressed? It seems a useful thing to be able to do without having to get into the DB-specific SQL.

[sergioisidoro]

+1 (Rails 7.x)

[msakr21]

+1 Rails 5.2.6 and ruby 2.7.4 with postgreSQL

[gap777]

@eileencodes helped me at RailsConf 2023 ATL this year to reproduce this using the following test scenario

In activerecord/test/cases/relation/update_all_tests.rb, add

def test_update_all_from_joined_table
    pets = Pet.joins(:toys)

    assert_equal true, pets.exists?
    pets.update_all("pets.name = toys.name")
  end

and execute test under psql with:

ARCONN=postgresql bundle exec ruby -Ilib:test test/cases/relation/update_all_test.rb -n test_update_all_from_joined_table

which fails in the now-expected manner:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "toys"