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

theshank

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?

11 comments

#ecto #postgres

0 6785 11

2017-11-11 09:09:47 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

OvermindDL1

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

Post #1

theshank

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?

Post #2

OvermindDL1

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

Post #3

theshank

Thanks.. will go with the raw sql query then.

Post #4

gregvaughn

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

Post #5

theshank

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.

Post #6

LostKobrakai

Ecto.Type.dump/2 might do that.

Post #7

theshank

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?

Post #8

voger

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. PostgreSQL: Documentation: 9.6: Date/Time Functions and Operators

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.

Post #9

theshank

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.

Post #10

Load more posts (1 remaining)