Skip to content

Difference between MySQL and PostgreSQL in multi-table UPDATE with a ambiguous field list #533

neglectedvalue opened this Issue Aug 13, 2012 · 1 comment

2 participants


Create two tables:

DB.create_table!(:tbl) do
  Integer :id
  Integer :parent_id
DB.create_table!(:tbl_tmp) do
  Integer :id
  Integer :parent_id

Then I try to do UPDATE:

>> DB[:tbl].from(:tbl_tmp, :tbl).update(:parent_id => 1)
Sequel::DatabaseError: Mysql2::Error: Column 'parent_id' in field list is ambiguous [/Users/user/.rvm/gems/ruby-1.9.3-p194/gems/sequel-3.36.1/lib/sequel/adapters/mysql2.rb:89:in `query']

Okay, I rewrote it to db[:tbl].from(:tbl_tmp, :tbl).update(:tbl_tmp__parent_id => 1) and it works.

But I need compatibility with PostgreSQL. As written in the PG's manual, I cannot use the table name:

Note that the target table must not appear in the from_list

And I see this:

>> DB[:tbl].from(:tbl_tmp, :tbl).update(:tbl_tmp__parent_id => 1)
Sequel::DatabaseError: PG::Error: ERROR:  column "tbl_tmp" of relation "tbl_tmp" does not exist
LINE 1: UPDATE "tbl_tmp" SET "tbl_tmp"."parent_id" = 1 FROM "tbl"
                             ^ [/Users/user/.rvm/gems/ruby-1.9.3-p194/gems/sequel-3.36.1/lib/sequel/adapters/postgres.rb:202:in `async_exec']

So, can Sequel::Dataset#update qualify column names if needed or I need to write a wrapper?


I think you'll need to write a wrapper.

Now, it's possible to automatically qualify keys in the hash passed to update to the first FROM table if multiple tables are present in FROM, but looking at the MySQL docs, that will probably break existing code, since you are allowed to reference columns from any table.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.