Using Sequel, can I create UPDATE statements with a FROM clause

Question

Using Sequel I'd like to create an UPDATE statement like the following (from Postgres docs)

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
  WHERE accounts.name = 'Acme Corporation'
  AND employees.id = accounts.sales_person;

However, looking at the Sequel documentation it's not clear where there's any way to create the FROM clause.

Is there a way to do this?

Thanks!

Mailable

Answer 1

OK, Ben Lee's answer got me going in the right direction. Solution:

DB[:employees].from(:employees, :accounts).
  where(:accounts__name => 'Acme Corporation').
  update_sql(:employees__sales_count => "employees.sales_count + 1".lit)

I'm a bit uncertain about the use of .lit here, but it does seem to do the trick. (Also, I'm using update_sql rather than update to produce the SQL output rather than running the command.)

Result, as desired:

UPDATE "employees" 
   SET "employees"."sales_count" = employees.sales_count + 1 
  FROM "accounts"
 WHERE ("accounts"."name" = 'Acme Corporation')

Mailable

Answer 2

Everybody else's answers have one tiny superfluous clause.

DB.from(:employees, :accounts).
  where(:accounts__name => 'Acme Corporation').
  update_sql(:employees__sales_count => "employees.sales_count + 1".lit)

If you're doing a from() with two tables, then the one inside the DB[...] is ignored.

Carob

Answer 3

Can you just use a regular join? It's not exactly your query, but it should accomplish your goals, right?

DB[:employees].left_outer_join(:accounts, :sales_person => :id).
    where('accounts.name' => 'Acme Corporation').
    update('employees.sales_count' => 'employees.sales_count' + 1)

Intercollegiate

Hmm, sounds promising! This produces the error Sequel::Error: Need multiple FROM tables if updating/deleting a dataset with JOINs. So now I just need to figure out how to add multiple FROMs – Mailable

Aha! yes, this can be done using DB[:employees].from(:employees, :account). More shortly. – Mailable