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()
...

In the particular case of SQL databases, you can also choose to use a builder or raw SQL query to execute update, insert, and delete operations.

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

< back
10/18
next >

              package main

import (
	"fmt"
	"log"

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

var settings = cockroachdb.ConnectionURL{
	Database: `booktown`,
	Host:     `cockroachdb.demo.upper.io`,
	User:     `demouser`,
	Password: `demop4ss`,
}

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 := cockroachdb.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("")
	}
}

            