Updating records in SQL while joining

I added a new column that I want to backfill with data from another table.

An example migration in Ecto:

defmodule App.Repo.Migrations.AddCentsToTransactions do use Ecto.Migration

def change do alter table(:transactions) do add(:price_cents, :integer) end end end

>
> What query do I need to run?


You can update a table based on other table data with the `FROM` keyword and perform a "join like" operation with a following `WHERE`.

In the example below we're filling in transactions based on the associated order data.

```sql
update transactions
set price_cents = orders.price_cents
from orders
where orders.id = transactions.order_id;

Used in a migration

defmodule App.Repo.Migrations.AddCentsToTransactions do
  use Ecto.Migration

  def change do
    alter table(:transactions) do
      add(:price_cents, :integer)
    end

    execute """
    update transactions
    set price_cents = orders.price_cents
    from orders
    where orders.id = transactions.order_id;
    """
  end
end

PostgreSQL Docs on Update

---

MySQL has similar syntax.

update transactions, orders
set price_cents = orders.price_cents
where orders.id = transactions.order_id;

Note the difference in where the orders table is included for querying.

MySQL Docs on Update