Is there any equivalent of `update ... from` syntax for Postgresql in Ecto?

Questions / Help
,
1

My use case is perfectly served by the update ... from syntax for Postgres as per the query below:

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

This is taken from this question on Stackoverflow.

Is there any way to achieve this using ecto?

2

I’m pretty sure Repo.update_all does that? I think?

3

I believe the “values” that will be there in the updated records when using update_all will be the same for all the updated records.

In my use case, I have a list of ids and the new value for a particular field for each of those ids. The next values are different for each id. Something like below:

[ [1, New_value_for_some_col_for_rec_1],
  [2, New_value_for_some_col_for_rec_2],
  ...
]

update_all wont work for this case… right?

1 Like
4

Good question, if it does not have a way to accept a list of updates, it should. ^.^;

5

Thanks… will go with the raw sql query then.

1 Like
6

hehe, yeah, I just did this in raw sql (via Ecto.Adapters.SQL.query!) about an hour ago :smiley:

7

Can you tell me how to convert (cast?) an elixir DateTime (say the value I get from of DateTime.utc_now()), into a Postgres column of type timestamp without time zone.

8

Ecto.Type.dump/2 might do that.

9

Thats great! I am new to the whole Elixir ecosystem and Ecto. Thank you for pointing me in the right direction.

Just to confirm, i can use the value obtained from Ecto.Type.dump(:utc_datetime, DateTime.utc_now()) in my raw sql query. Does my understanding appear correct?

10

Why? SQL has it’s own functions for datetime if you want to go that route.
I am a noob too when it comes to SQL.

Here are PostgreSQL date functions if you use PostgreSQL. https://www.postgresql.org/docs/9.6/static/functions-datetime.html

Also I am not sure what exactly does your query do in your first post. As I said I am a noob also. But I had a situation recently where I had to update multiple records with different values. I ended reading those records from the database (Repo.all(query...)) then iterating over them and appending in a Multi and finally applying everything in a transaction. A total of 2 database transactions. No need for raw SQL.

I don’t know if it is proper or if there are better ways. Just throwing an idea.

11

Thanks for both the suggestions!

Using Ecto.Multi might also be a good option… My use-case is to update a few thousand records every minute with new values. These records are just recurring “jobs” that have a “scheduled_time”. Every minute, the jobs that have completed in the past minute are updated with the new values for the “scheduled_time” for the next run.

1 Like
12

@theshank Have you tried Ecto.Adapters.SQL.query? Here’s a blog post that shows how.

Bulk update unique values with Ecto and PostgreSQL