CODEWITHSUNDEEP
mysqlgogo-gorm
Soumil Kanwal
Soumil Kanwal

Reputation: 93

How to combine multiple update GORM MySql queries into 1?

I have a MySQL table containing 4 columns

(A(primary key, int), B(string), C(string), D(string)).

I want to run an update query finding row using A, where I can update data of 1, 2, or all 3 columns(B, C, D). I get the data from the front-end in JSON format. Currently, I am checking the length of strings I get in input, and if they are >0, I update those columns one by one. So in the worst case, I will be running 3 SQL updates. Is there a way to do this in 1 SQL query using GORM, and Golang?

Example JSON structure
{
A: 23,
B: "word1",
D: "word2"
}

In this case, I only want to update columns B and D.

I will unmarshal this using a Golang structure which will be of the form

type rating struct{
    A int,
    B string,
    C string,
    D string
        }

So in Golang value of C will be an empty string.

var rating_var rating
if len(rating_var.B)>0{
       db.Model(&ratings).where("A=?",rating_var.A).Update(map[struct]interface{}{"B": rating_var.B})
        }

if len(rating_var.C)>0{
       db.Model(&ratings).where("A=?",rating_var.A).Update(map[struct]interface{}{"C": rating_var.C})
        }

if len(rating_var.D)>0{
       db.Model(&ratings).where("A=?",rating_var.A).Update(map[struct]interface{}{"D": rating_var.D})
        }

Is there a way to do this in 1 SQL query using GORM, and Golang?

Upvotes: 0

Views: 2880

Answers (2)

Shailesh Suryawanshi
Shailesh Suryawanshi

Reputation: 1270

I would suggest to build a Model according to your conditions first. Use that model to run only one SQL query. Check the complete example on playground here.

Note that gorm will take care of some missing fields. Now if the rating.C is blank, gorm will not update the value of c column in record.

        rating := Rating{
                A: 1,
                B: "b1",
                D: "d2",
        }

        var toUpdate RatingModel
        if len(rating.B) > 0 {
                toUpdate.B = rating.B
        }

        if len(rating.C) > 0 {
                toUpdate.C = rating.C
        }

        if len(rating.D) > 0 {
                toUpdate.D = rating.D
        }
        db.Model(&toUpdate).Where("A=?", rating.A).Updates(toUpdate)

I would suggest using structs over map. Go is strongly typed language thus using structs is more idiomatic.

Upvotes: 2

zhenhua32
zhenhua32

Reputation: 264

You can use Updates to update multiple fields.

db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})

db.Model(&ratings).where("A=?",rating_var.A).Updates(map[struct]interface{}{"B": rating_var.B, "C": rating_var.C})

Updates

Upvotes: 0

Related Questions