deleting.md

Deleting

• Creating A DeleteDataset
• Examples
  • Delete All
  • Prepared
  • Where
  • Order
  • Limit
  • Returning
  • SetError
  • Executing

To create a DeleteDataset you can use

goqu.Delete

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

sql, _, _ := goqu.Delete("table").ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "table"

SelectDataset.Delete

If you already have a SelectDataset you can invoke Delete() to get a DeleteDataset

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

ds := goqu.From("table")

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

sql, _, _ = ds.Where(goqu.C("foo").Eq("bar")).Delete().ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "table"
DELETE FROM "table" WHERE "foo"='bar'

DialectWrapper.Delete

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")

sql, _, _ := dialect.Delete("table").ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `table`

Database.Delete

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)

sql, _, _ := db.Delete("table").ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `table`

Examples

For more examples visit the Docs

Delete All Records

ds := goqu.Delete("items")

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

Output:

DELETE FROM "items" []

Prepared

sql, _, _ := goqu.Delete("test").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:

DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]

Where

sql, _, _ := goqu.Delete("test").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:

DELETE FROM "test" 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").Delete("test").Order(goqu.C("a").Asc())
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test` ORDER BY `a` ASC

Limit

NOTE This will only work if your dialect supports it

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

ds := goqu.Dialect("mysql").Delete("test").Limit(10)
sql, _, _ := ds.ToSQL()
fmt.Println(sql)

Output:

DELETE FROM `test` LIMIT 10

Returning

Returning a single column example.

ds := goqu.Delete("items")
sql, args, _ := ds.Returning("id").ToSQL()
fmt.Println(sql, args)

Output:

DELETE FROM "items" RETURNING "id" []

Returning multiple columns

sql, _, _ := goqu.Delete("test").Returning("a", "b").ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "items" RETURNING "a", "b"

Returning all columns

sql, _, _ := goqu.Delete("test").Returning(goqu.T("test").All()).ToSQL()
fmt.Println(sql)

Output:

DELETE FROM "test" 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 GetDelete(name string, value string) *goqu.DeleteDataset {

    var ds = goqu.Delete("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.Where(goqu.C(name).Eq(value))
}

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

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

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

Output:

name is empty
name is empty

Executing Deletes

To execute DELETES use Database.Delete to create your dataset

Examples

Executing a Delete

db := getDb()

de := db.Delete("goqu_user").
	Where(goqu.Ex{"first_name": "Bob"}).
	Executor()

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

Output:

Deleted 1 users

If you use the RETURNING clause you can scan into structs or values.

db := getDb()

de := db.Delete("goqu_user").
	Where(goqu.C("last_name").Eq("Yukon")).
	Returning(goqu.C("id")).
	Executor()

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

Output:

Deleted users [ids:=[1 2 3]]