Gorm Use FROM clause on update (PostgreSQL)

Your Question

Hi How add a FROM clause on update to use PostgreSQL join ability on update? I don't wanna use sub-query or separate query because of performance.

UPDATE income
SET amount = 1000, status = 0, ..., ..., ..., ..., ..., ..., ..., ...
FROM account
WHERE income.id = 1 AND income.account_id = account.id AND account.user_id = 200

https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql

The document you expected this should be explained

https://gorm.io/docs/update.html https://gorm.io/docs/sql_builder.html

Expected answer

db.Select(fields).Omit("id", "created_at", "created_by").From("account").
    Where("income.id = ? AND income.account_id = account.id AND account.user_id = ?",
        income.id, userID).Model(&Income{}).Updates(&income)

OR

db.Select(fields).Omit("id", "created_at", "created_by").Joins("FROM account").
    Where("income.id = ? AND income.account_id = account.id AND account.user_id = ?",
        income.id, userID).Model(&Income{}).Updates(&income)

OR

db.Clauses(clause.From{Tables: []clause.Table{{Name: "account"}}}).
    Select(fields).Omit("id", "created_at", "created_by").
    Where("income.id = ? AND income.account_id = account.id AND account.user_id = ?",
        income.id, userID).Model(&Income{}).Updates(&income)

OR

db.Clauses(clause.Expr{SQL: "FROM account"}).
    Select(fields).Omit("id", "created_at", "created_by").
    Where("income.id = ? AND income.account_id = account.id AND account.user_id = ?",
        income.id, userID).Model(&Income{}).Updates(&income)

The last one is adds FROM after WHERE .Updates method adds ID column in WHERE clause that it's ambiguous when using FROM, I prevented it by using .Model(&Income{})

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days

Comment From: L1ghtman2k

@Pourqavam hey, have you figured out how to do it? This seems like a very elegant way to perform both validation, and update at the same time

Comment From: L1ghtman2k

There is a resolution to this question in: https://github.com/go-gorm/gorm/issues/4445, now I am not sure if it is the best resolution out there, but it works:

I will modify your 3rd example to make it work:

tx := db.Clauses(clause.From{Tables: []clause.Table{{Name: "account"}}}).
    Select(fields).Omit("id", "created_at", "created_by").
    Where("income.id = ? AND income.account_id = account.id AND account.user_id = ?",
        income.id, userID).Model(&Income{})
tx.Statement.BuildClauses = []string{clause.Update{}.Name(), clause.Set{}.Name(), clause.From{}.Name(), clause.Where{}.Name()}
tx.Updates(&income)

Comment From: L1ghtman2k

Also, just in case you need to dynamically figure out the table name, this may help: https://github.com/go-gorm/gorm/issues/3603#issuecomment-709883403

Comment From: L1ghtman2k

In case of a simple retrieval, like select, the following query might work:

    prop := &property.Property{}
    tx2 := db.Debug().Select(fmt.Sprintf("%s.*", propertyTableName)).Clauses(clause.From{Tables: []clause.Table{{Name: serviceTableName}, {Name: hostTableName}, {Name: teamTableName}, {Name: propertyTableName}}}).
        Where(fmt.Sprintf("%s.id = %s.host_id AND %s.id = %s.service_id AND %s.key = ? AND %s.service_id = ? AND %s.id = ?",hostTableName,serviceTableName,serviceTableName,propertyTableName,propertyTableName,propertyTableName,teamTableName),
            "Username", uuid.FromStringOrNil("ff783e2b-ddd2-4d24-82ee-53f58c1be716"),uuid.FromStringOrNil("03302d0b-c28d-444a-9b9b-0440c3d711e3")).First(prop)
    if tx2.Error != nil {
        panic(tx2.Error)
    }
    fmt.Println(*prop.Value)

Retreiving values of the specific table only: Select(fmt.Sprintf("%s.*", propertyTableName)). Listing multiple tables to be retrieved from: Clauses(clause.From{Tables: []clause.Table{{Name: serviceTableName}, {Name: hostTableName}, {Name: teamTableName}, {Name: propertyTableName}}}).

Comment From: pourqavam

@Pourqavam hey, have you figured out how to do it? This seems like a very elegant way to perform both validation, and update at the same time

Hi No, I did it manually to create the query dynamically and by using Exec method, but thank you for your workaround. I think it can be easier if GORM does something like that internally.

Comment From: jinzhu

https://github.com/go-gorm/gorm/issues/4445#issuecomment-855542337

Comment From: jinzhu

https://github.com/go-gorm/gorm/blob/master/statement.go#L56

Comment From: dhalturin

This may be useful to someone if you need to update the value of a field with the value of a field from another table

tx := db.Clauses(clause.From{
    Tables: []clause.Table{
        {Name: "category_relations"},
    },
}).Select("category_id").Omit("updated_at").
    Where("category_relations.name = store_products.store_category and store_id = ?", crawlers[crawler]).
    Model(&models.StoreProduct{})
tx.Statement.BuildClauses = []string{
    clause.Update{}.Name(),
    clause.Set{}.Name(),
    clause.From{}.Name(),
    clause.Where{}.Name(),
}
err := tx.UpdateColumn("category_id", gorm.Expr("category_relations.category_id")).Error

result

UPDATE "store_products"
SET "category_id"=category_relations.category_id
FROM "category_relations"
WHERE
    (category_relations.name = store_products.store_category and store_id = 1) AND
    "store_products"."deleted_at" IS NULL