Updating

• Create a UpdateDataset
• Examples
  • Set with goqu.Record
  • Set with struct
  • Set with map
  • Multi Table
  • Where
  • Order
  • Limit
  • Returning
  • SetError
  • Executing

To create a UpdateDataset you can use

goqu.Update

When you just want to create some quick SQL, this mostly follows the Postgres with the exception of placeholders for prepared statements.

ds := goqu.Update("user").Set(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
)
updateSQL, _, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

UPDATE "user" SET "first_name"='Greg', "last_name"='Farley'

SelectDataset.Update

If you already have a SelectDataset you can invoke Update() to get a UpdateDataset

NOTE This method will also copy over the WITH, WHERE, ORDER, and LIMIT clauses from the update

ds := goqu.From("user")

updateSQL, _, _ := ds.Update().Set(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
).ToSQL()
fmt.Println(insertSQL, args)

updateSQL, _, _ = ds.Where(goqu.C("first_name").Eq("Gregory")).Update().Set(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
).ToSQL()
fmt.Println(insertSQL, args)

Output:

UPDATE "user" SET "first_name"='Greg', "last_name"='Farley'
UPDATE "user" SET "first_name"='Greg', "last_name"='Farley' WHERE "first_name"='Gregory'

DialectWrapper.Update

Use this when you want to create SQL for a specific dialect

// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"

dialect := goqu.Dialect("mysql")

ds := dialect.Update("user").Set(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
)
updateSQL, _, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

UPDATE `user` SET `first_name`='Greg', `last_name`='Farley'

Database.Update

Use this when you want to execute the SQL or create SQL for the drivers dialect.

// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"

mysqlDB := //initialize your db
db := goqu.New("mysql", mysqlDB)

ds := db.Update("user").Set(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
)
updateSQL, _, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

UPDATE `user` SET `first_name`='Greg', `last_name`='Farley'

Examples

For more examples visit the Docs

Set with goqu.Record

sql, args, _ := goqu.Update("items").Set(
	goqu.Record{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []

Set with Struct

type item struct {
	Address string `db:"address"`
	Name    string `db:"name"`
}
sql, args, _ := goqu.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []

With structs you can also skip fields by using the skipupdate tag

type item struct {
	Address string `db:"address"`
	Name    string `db:"name" goqu:"skipupdate"`
}
sql, args, _ := goqu.Update("items").Set(
	item{Name: "Test", Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr' []

If you do not want to update the database field when the struct field is a nil pointer you can use the omitnil tag. This allows a struct of pointers to be used to represent partial updates where nil pointers were not changed.

type item struct {
    FirstName string  `db:"first_name" goqu:"omitnil"`
    LastName  string  `db:"last_name" goqu:"omitnil"`
    Address1  *string `db:"address1" goqu:"omitnil"`
    Address2  *string `db:"address2" goqu:"omitnil"`
    Address3  *string `db:"address3" goqu:"omitnil"`
}
address1 := "113 Test Addr"
var emptyString string
sql, args, _ := goqu.Update("items").Set(
    item{
        FirstName: "Test First Name",
        LastName:  "",
        Address1:  &address1,
        Address2:  &emptyString,
        Address3:  nil, // will omit nil pointer
    },
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address1"='113 Test Addr',"address2"='',"first_name"='Test First Name',"last_name"='' []

If you do not want to update the database field when the struct field is a zero value (including nil pointers) you can use the omitempty tag.

Empty embedded structs implementing the Valuer interface (eg. sql.NullString) will also be omitted.

type item struct {
    FirstName string  `db:"first_name" goqu:"omitempty"`
    LastName  string  `db:"last_name" goqu:"omitempty"`
    Address1  *string `db:"address1" goqu:"omitempty"`
    Address2  *string `db:"address2" goqu:"omitempty"`
    Address3  *string `db:"address3" goqu:"omitempty"`
}
address1 := "114 Test Addr"
var emptyString string
sql, args, _ := goqu.Update("items").Set(
    item{
        FirstName: "Test First Name",
        LastName:  "", // will omit zero field
        Address1:  &address1,
        Address2:  &emptyString,
        Address3:  nil, // will omit nil pointer
    },
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address1"='114 Test Addr',"address2"='',"first_name"='Test First Name' []

If you want to use the database DEFAULT when the struct field is a zero value you can use the defaultifempty tag.

type item struct {
	Address string `db:"address"`
	Name    string `db:"name" goqu:"defaultifempty"`
}
sql, args, _ := goqu.Update("items").Set(
	item{Address: "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"=DEFAULT []

goqu will also use fields in embedded structs when creating an update.

NOTE unexported fields will be ignored!

type Address struct {
	Street string `db:"address_street"`
	State  string `db:"address_state"`
}
type User struct {
	Address
	FirstName string
	LastName  string
}
ds := goqu.Update("user").Set(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
)
updateSQL, args, _ := ds.ToSQL()
fmt.Println(updateSQL, args)

Output:

UPDATE "user" SET "address_state"='NY',"address_street"='111 Street',"firstname"='Greg',"lastname"='Farley' []

NOTE When working with embedded pointers if the embedded struct is nil then the fields will be ignored.

type Address struct {
	Street string
	State  string
}
type User struct {
	*Address
	FirstName string
	LastName  string
}
ds := goqu.Update("user").Set(
	User{FirstName: "Greg", LastName: "Farley"},
)
updateSQL, args, _ := ds.ToSQL()
fmt.Println(updateSQL, args)

Output:

UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []

You can ignore an embedded struct or struct pointer by using db:"-"

type Address struct {
	Street string
	State  string
}
type User struct {
	Address   `db:"-"`
	FirstName string
	LastName  string
}
ds := goqu.Update("user").Set(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
)
updateSQL, args, _ := ds.ToSQL()
fmt.Println(updateSQL, args)

Output:

UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []

Set with Map

sql, args, _ := goqu.Update("items").Set(
	map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []

Set with Expressions

sql, args, _ := goqu.Update("items").Set([]exp.UpdateExpression{
	goqu.C("name").Set("Test"),
	goqu.C("address").Set("111 Test Addr"),
}).ToSQL()
fmt.Println(sql, args)

Output:

UPDATE "items" SET "name"='Test',"address"='111 Test Addr' []

From / Multi Table

goqu allows joining multiple tables in a update clause through From.

NOTE The sqlite3 adapter does not support a multi table syntax.

Postgres Example

dialect := goqu.Dialect("postgres")

ds := dialect.Update("table_one").
    Set(goqu.Record{"foo": goqu.I("table_two.bar")}).
    From("table_two").
    Where(goqu.Ex{"table_one.id": goqu.I("table_two.id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id")

MySQL Example

dialect := goqu.Dialect("mysql")

ds := dialect.Update("table_one").
    Set(goqu.Record{"foo": goqu.I("table_two.bar")}).
    From("table_two").
    Where(goqu.Ex{"table_one.id": goqu.I("table_two.id")})

sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE `table_one`,`table_two` SET `foo`=`table_two`.`bar` WHERE (`table_one`.`id` = `table_two`.`id`)

Where

sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Where(goqu.Ex{
		"a": goqu.Op{"gt": 10},
		"b": goqu.Op{"lt": 10},
		"c": nil,
		"d": []string{"a", "b", "c"},
	}).ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))

Order

NOTE This will only work if your dialect supports it

// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"

ds := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Order(goqu.C("a").Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC

Order

NOTE This will only work if your dialect supports it

// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"

ds := goqu.Dialect("mysql").
	Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Limit(10)
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

UPDATE `test` SET `foo`='bar' LIMIT 10

Returning

Returning a single column example.

sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Returning("id").
	ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' RETURNING "id"

Returning multiple columns

sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Returning("a", "b").
	ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' RETURNING "a", "b"

Returning all columns

sql, _, _ := goqu.Update("test").
	Set(goqu.Record{"foo": "bar"}).
	Returning(goqu.T("test").All()).
	ToSQL()
fmt.Println(sql)

Output:

UPDATE "test" SET "foo"='bar' RETURNING "test".*

SetError

Sometimes while building up a query with goqu you will encounter situations where certain preconditions are not met or some end-user contraint has been violated. While you could track this error case separately, goqu provides a convenient built-in mechanism to set an error on a dataset if one has not already been set to simplify query building.

Set an Error on a dataset:

func GetUpdate(name string, value string) *goqu.UpdateDataset {

    var ds = goqu.Update("test")

    if len(name) == 0 {
        return ds.SetError(fmt.Errorf("name is empty"))
    }

    if len(value) == 0 {
        return ds.SetError(fmt.Errorf("value is empty"))
    }

    return ds.Set(goqu.Record{name: value})
}

This error is returned on any subsequent call to Error or ToSQL:

var field, value string
ds = GetUpdate(field, value)
fmt.Println(ds.Error())

sql, args, err = ds.ToSQL()
fmt.Println(err)

Output:

name is empty
name is empty

Executing Updates

To execute Updates use goqu.Database#Update to create your dataset

Examples

Executing an update

db := getDb()

update := db.Update("goqu_user").
	Where(goqu.C("first_name").Eq("Bob")).
	Set(goqu.Record{"first_name": "Bobby"}).
	Executor()

if r, err := update.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	c, _ := r.RowsAffected()
	fmt.Printf("Updated %d users", c)
}

Output:

Updated 1 users

Executing with Returning

db := getDb()

update := db.Update("goqu_user").
	Set(goqu.Record{"last_name": "ucon"}).
	Where(goqu.Ex{"last_name": "Yukon"}).
	Returning("id").
	Executor()

var ids []int64
if err := update.ScanVals(&ids); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Updated users with ids %+v", ids)
}

Output:

Updated users with ids [1 2 3]