1# Deleting
2
3* [Creating A DeleteDataset](#create)
4* Examples
5 * [Delete All](#delete-all)
6 * [Prepared](#prepared)
7 * [Where](#where)
8 * [Order](#order)
9 * [Limit](#limit)
10 * [Returning](#returning)
11 * [SetError](#seterror)
12 * [Executing](#exec)
13
14<a name="create"></a>
15To create a [`DeleteDataset`](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset) you can use
16
17**[`goqu.Delete`](https://godoc.org/github.com/doug-martin/goqu/#Delete)**
18
19When you just want to create some quick SQL, this mostly follows the `Postgres` with the exception of placeholders for prepared statements.
20
21```go
22sql, _, _ := goqu.Delete("table").ToSQL()
23fmt.Println(sql)
24```
25Output:
26```
27DELETE FROM "table"
28```
29
30**[`SelectDataset.Delete`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Delete)**
31
32If you already have a `SelectDataset` you can invoke `Delete()` to get a `DeleteDataset`
33
34**NOTE** This method will also copy over the `WITH`, `WHERE`, `ORDER`, and `LIMIT` from the `SelectDataset`
35
36```go
37
38ds := goqu.From("table")
39
40sql, _, _ := ds.Delete().ToSQL()
41fmt.Println(sql)
42
43sql, _, _ = ds.Where(goqu.C("foo").Eq("bar")).Delete().ToSQL()
44fmt.Println(sql)
45```
46Output:
47```
48DELETE FROM "table"
49DELETE FROM "table" WHERE "foo"='bar'
50```
51
52**[`DialectWrapper.Delete`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Delete)**
53
54Use this when you want to create SQL for a specific `dialect`
55
56```go
57// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
58
59dialect := goqu.Dialect("mysql")
60
61sql, _, _ := dialect.Delete("table").ToSQL()
62fmt.Println(sql)
63```
64Output:
65```
66DELETE FROM `table`
67```
68
69**[`Database.Delete`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Delete)**
70
71Use this when you want to execute the SQL or create SQL for the drivers dialect.
72
73```go
74// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
75
76mysqlDB := //initialize your db
77db := goqu.New("mysql", mysqlDB)
78
79sql, _, _ := db.Delete("table").ToSQL()
80fmt.Println(sql)
81```
82Output:
83```
84DELETE FROM `table`
85```
86
87### Examples
88
89For more examples visit the **[Docs](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset)**
90
91<a name="delete-all"></a>
92**Delete All Records**
93
94```go
95ds := goqu.Delete("items")
96
97sql, args, _ := ds.ToSQL()
98fmt.Println(sql, args)
99```
100
101Output:
102```
103DELETE FROM "items" []
104```
105
106<a name="prepared"></a>
107**[`Prepared`](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset.Prepared)**
108
109```go
110sql, _, _ := goqu.Delete("test").Where(goqu.Ex{
111 "a": goqu.Op{"gt": 10},
112 "b": goqu.Op{"lt": 10},
113 "c": nil,
114 "d": []string{"a", "b", "c"},
115}).ToSQL()
116fmt.Println(sql)
117```
118
119Output:
120```
121DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
122```
123
124<a name="where"></a>
125**[`Where`](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset.Where)**
126
127```go
128sql, _, _ := goqu.Delete("test").Where(goqu.Ex{
129 "a": goqu.Op{"gt": 10},
130 "b": goqu.Op{"lt": 10},
131 "c": nil,
132 "d": []string{"a", "b", "c"},
133}).ToSQL()
134fmt.Println(sql)
135```
136
137Output:
138```
139DELETE FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
140```
141
142<a name="order"></a>
143**[`Order`](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset.Order)**
144
145**NOTE** This will only work if your dialect supports it
146
147```go
148// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
149
150ds := goqu.Dialect("mysql").Delete("test").Order(goqu.C("a").Asc())
151sql, _, _ := ds.ToSQL()
152fmt.Println(sql)
153```
154
155Output:
156```
157DELETE FROM `test` ORDER BY `a` ASC
158```
159
160<a name="limit"></a>
161**[`Limit`](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset.Limit)**
162
163**NOTE** This will only work if your dialect supports it
164
165```go
166// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
167
168ds := goqu.Dialect("mysql").Delete("test").Limit(10)
169sql, _, _ := ds.ToSQL()
170fmt.Println(sql)
171```
172
173Output:
174```
175DELETE FROM `test` LIMIT 10
176```
177
178<a name="returning"></a>
179**[`Returning`](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset.Returning)**
180
181Returning a single column example.
182
183```go
184ds := goqu.Delete("items")
185sql, args, _ := ds.Returning("id").ToSQL()
186fmt.Println(sql, args)
187```
188
189Output:
190```
191DELETE FROM "items" RETURNING "id" []
192```
193
194Returning multiple columns
195
196```go
197sql, _, _ := goqu.Delete("test").Returning("a", "b").ToSQL()
198fmt.Println(sql)
199```
200
201Output:
202```
203DELETE FROM "items" RETURNING "a", "b"
204```
205
206Returning all columns
207
208```go
209sql, _, _ := goqu.Delete("test").Returning(goqu.T("test").All()).ToSQL()
210fmt.Println(sql)
211```
212
213Output:
214```
215DELETE FROM "test" RETURNING "test".*
216```
217
218<a name="seterror"></a>
219**[`SetError`](https://godoc.org/github.com/doug-martin/goqu/#DeleteDataset.SetError)**
220
221Sometimes while building up a query with goqu you will encounter situations where certain
222preconditions are not met or some end-user contraint has been violated. While you could
223track this error case separately, goqu provides a convenient built-in mechanism to set an
224error on a dataset if one has not already been set to simplify query building.
225
226Set an Error on a dataset:
227
228```go
229func GetDelete(name string, value string) *goqu.DeleteDataset {
230
231 var ds = goqu.Delete("test")
232
233 if len(name) == 0 {
234 return ds.SetError(fmt.Errorf("name is empty"))
235 }
236
237 if len(value) == 0 {
238 return ds.SetError(fmt.Errorf("value is empty"))
239 }
240
241 return ds.Where(goqu.C(name).Eq(value))
242}
243
244```
245
246This error is returned on any subsequent call to `Error` or `ToSQL`:
247
248```go
249var field, value string
250ds = GetDelete(field, value)
251fmt.Println(ds.Error())
252
253sql, args, err = ds.ToSQL()
254fmt.Println(err)
255```
256
257Output:
258```
259name is empty
260name is empty
261```
262
263## Executing Deletes
264
265To execute DELETES use [`Database.Delete`](https://godoc.org/github.com/doug-martin/goqu/#Database.Delete) to create your dataset
266
267### Examples
268
269<a name="exec"></a>
270**Executing a Delete**
271```go
272db := getDb()
273
274de := db.Delete("goqu_user").
275 Where(goqu.Ex{"first_name": "Bob"}).
276 Executor()
277
278if r, err := de.Exec(); err != nil {
279 fmt.Println(err.Error())
280} else {
281 c, _ := r.RowsAffected()
282 fmt.Printf("Deleted %d users", c)
283}
284```
285
286Output:
287
288```
289Deleted 1 users
290```
291
292If you use the RETURNING clause you can scan into structs or values.
293
294```go
295db := getDb()
296
297de := db.Delete("goqu_user").
298 Where(goqu.C("last_name").Eq("Yukon")).
299 Returning(goqu.C("id")).
300 Executor()
301
302var ids []int64
303if err := de.ScanVals(&ids); err != nil {
304 fmt.Println(err.Error())
305} else {
306 fmt.Printf("Deleted users [ids:=%+v]", ids)
307}
308```
309
310Output:
311
312```
313Deleted users [ids:=[1 2 3]]
314```
View as plain text