6

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!

brahn
  • 12,096
  • 11
  • 39
  • 49

3 Answers3

5

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')
brahn
  • 12,096
  • 11
  • 39
  • 49
3

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.

ColinK
  • 438
  • 1
  • 3
  • 8
0

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)
Ben Lee
  • 52,489
  • 13
  • 125
  • 145
  • 1
    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 – brahn Feb 02 '13 at 01:48
  • Aha! yes, this can be done using `DB[:employees].from(:employees, :account)`. More shortly. – brahn Feb 02 '13 at 01:53