Apparently there is an issue in Arel core, where Arel::UpdateManager, when performing a column update on a join, does not generate the table name for the update column. It results in invalid SQL.
I ran into this in a Rails 5.2 app, where I had an SQL literal UPDATE statement that I was trying to rephrase in Arel.
UPDATE observations o, names n
SET o.lifeform = n.lifeform
WHERE o.name_id = n.id
AND o.lifeform != n.lifeform
In Arel, i wrote this:
names = Name.arel_table
obs = Observation.arel_table
join_source = Arel::Nodes::JoinSource.new(
obs, [obs.create_join(names)]
)
Arel::UpdateManager.new.
table(join_source).
where(obs[:id].eq(names[:id]).
and(obs[:lifeform].not_eq(names[:lifeform]))).
set([[obs[:lifeform], names[:lifeform]]])
This returns:
Mysql2::Error: Column 'lifeform' in field list is ambiguous:
The problem is at the end. The SQL generated from this does not specify the table where the column is to be set.
UPDATE `observations`
INNER JOIN `names`
SET `lifeform` = `names`.`lifeform`
WHERE (`observations`.`id` = `names`.`id`)
AND (`observations`.`lifeform` != `names`.`lifeform`)
Elsewhere, Arel-generated SQL usually qualifies columns with table names to avoid ambiguity. But the source code for update_manager.rb definitely uses Nodes::UnqualifiedColumn.new(column). (I have added my description to the Arel issue on GitHub.)
For now I'd maybe like to rephrase my Arel some other way. Is there a way to force Arel to quote the table name, similar to connection.quote_table_name?
Or would using a CTE be appropriate?
I guess one way to do this is with ActiveRecord's connection.update_all.
names = Arel::Table.new(:names)
Observation.joins(:name).
where(names[:correct_spelling_id].not_eq(nil)).
update_all("`observations`.`name_id` = `names`.`correct_spelling_id`")
This generates the desired SQL:
UPDATE `observations`
INNER JOIN `names`
ON (`observations`.`name_id` = `names`.`correct_spelling_id`)
AND (`names`.`correct_spelling_id` IS NOT NULL)
SET `observations`.`name_id` = `names`.`correct_spelling_id`
I think this is the way to go.