Update, insert, or delete records in a result set

The records in result sets can not only be queried but also modified and removed.

If you want to modify the properties of a whole result set, use Update:

var book Book
res := booksCol.Find(4267)

err = res.One(&book)
...

book.Title = "New title"

err = res.Update(book)
...

Note that the result above set consists of only one element, whereas the next result set consists of all the records in the collection:

res := booksCol.Find()

// Updating all records in the result set.
err := res.Update(map[string]int{
  "author_id": 23,
})

If you want to remove all the records in a result set, use Delete:

res := booksCol.Find(4267)

err := res.Delete()
// ...

As with the Update examples, in the previous case, only one record will be affected, and in the following scenario, all records will be deleted:

res := booksCol.Find()

//  Deleting all records in the result-set.
err := res.Delete()
...

Given that the examples in this tour are based on an SQL database, we’ll elaborate on the use of both a) SQL builder methods and b) raw SQL statements.

< back
12/18
next >

              package main

import (
	"fmt"
	"log"

	"github.com/upper/db/v4"
	"github.com/upper/db/v4/adapter/postgresql"
)

var settings = postgresql.ConnectionURL{
	Database: "booktown",
	Host:     "postgres",
	User:     "demo",
	Password: "b4dp4ss",
	Options: map[string]string{
		"sslmode": "disable", // Disable TLS
	},
}

type Book struct {
	ID        uint   `db:"id"`
	Title     string `db:"title"`
	AuthorID  uint   `db:"author_id"`
	SubjectID uint   `db:"subject_id"`
}

func main() {
	db.LC().SetLevel(db.LogLevelDebug)

	sess, err := postgresql.Open(settings)
	if err != nil {
		log.Fatal("Open: ", err)
	}
	defer sess.Close()

	booksTable := sess.Collection("books")

	// This result set includes a single record.
	res := booksTable.Find(4267)

	// The record is retrieved with the given ID.
	var book Book
	err = res.One(&book)
	if err != nil {
		log.Fatal("Find: ", err)
	}

	fmt.Printf("Book: %#v", book)

	// A change is made to a property.
	book.Title = "New title"

	fmt.Printf("Book (modified): %#v", book)
	fmt.Println("")

	// The result set is updated.
	if err := res.Update(book); err != nil {
		fmt.Printf("Update: %v\n", err)
		fmt.Printf("This is OK, we're running on a sandbox with a read-only database.\n")
		fmt.Println("")
	}

	// The result set is deleted.
	if err := res.Delete(); err != nil {
		fmt.Printf("Delete: %v\n", err)
		fmt.Printf("This is OK, we're running on a sandbox with a read-only database.\n")
		fmt.Println("")
	}
}

            