How can we execute using Golang multiple update statements

#1

How can we execute using Golang multiple update statements like

UPDATE $userName SET P1 = '$p1MON' WHERE day = 'MON1';
        UPDATE $userName SET P1 = '$p1TUE' WHERE day = 'TUE1';
        UPDATE $userName SET P1 = '$p1WED' WHERE day = 'WED1'

I know, we can db.prepare for multiple insert statements, wondering about update statuements

1 Like
#2

Hello Kiran,

The standard package doesn’t provide you advanced features of ORM or a driver library. You can use bulk updates provided by your driver library.

I don’t know which database you are using at the moment but I am assuming it is postgreSQL. With a driver library, you can easily do your bulk updates.

I have also found the raw SQL to generate a bulk update so you can use it within database/sql if you do not wish to use a driver library

Additional note: db.prepare doesn’t make you have bulk insert out of the box. It’s sole purpose is to have a transaction ongoing for your database.

See the example:
https://pkg.go.dev/github.com/go-pg/pg#example-DB-Update-BulkUpdate

   db := modelDB()

book1 := &Book{
	Id:        1,
	Title:     "updated book 1",
	UpdatedAt: time.Now(),
}
book2 := &Book{
	Id:        2,
	Title:     "updated book 2",
	UpdatedAt: time.Now(),
}

// UPDATE "books" AS "book"
// SET "title" = _data."title"
// FROM (VALUES ('updated book 1', 1), ('updated book 2', 2)) AS _data("title", "id")
// WHERE "book"."id" = _data."id"
_, err := db.Model(book1, book2).Column("title", "updated_at").Update()
if err != nil {
	panic(err)
}

var books []Book
err = db.Model(&books).Order("id").Select()
if err != nil {
	panic(err)
}

fmt.Println(books)
#3

Using vanilla SQL

UPDATE users SET username =
CASE day
WHEN 'MON1' THEN '$p1MON'
WHEN 'TUE1' THEN '$p1TUE'
WHEN 'WED1' THEN '$p1WED'
ELSE '?' END;

https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/18