1# Updating
2
3* [Create a UpdateDataset](#create)
4* Examples
5 * [Set with `goqu.Record`](#set-record)
6 * [Set with struct](#set-struct)
7 * [Set with map](#set-map)
8 * [Multi Table](#from)
9 * [Where](#where)
10 * [Order](#order)
11 * [Limit](#limit)
12 * [Returning](#returning)
13 * [SetError](#seterror)
14 * [Executing](#executing)
15
16<a name="create"></a>
17To create a [`UpdateDataset`](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset) you can use
18
19**[`goqu.Update`](https://godoc.org/github.com/doug-martin/goqu/#Update)**
20
21When you just want to create some quick SQL, this mostly follows the `Postgres` with the exception of placeholders for prepared statements.
22
23```go
24ds := goqu.Update("user").Set(
25 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
26)
27updateSQL, _, _ := ds.ToSQL()
28fmt.Println(insertSQL, args)
29```
30Output:
31```
32UPDATE "user" SET "first_name"='Greg', "last_name"='Farley'
33```
34
35**[`SelectDataset.Update`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Update)**
36
37If you already have a `SelectDataset` you can invoke `Update()` to get a `UpdateDataset`
38
39**NOTE** This method will also copy over the `WITH`, `WHERE`, `ORDER`, and `LIMIT` clauses from the update
40
41```go
42ds := goqu.From("user")
43
44updateSQL, _, _ := ds.Update().Set(
45 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
46).ToSQL()
47fmt.Println(insertSQL, args)
48
49updateSQL, _, _ = ds.Where(goqu.C("first_name").Eq("Gregory")).Update().Set(
50 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
51).ToSQL()
52fmt.Println(insertSQL, args)
53```
54Output:
55```
56UPDATE "user" SET "first_name"='Greg', "last_name"='Farley'
57UPDATE "user" SET "first_name"='Greg', "last_name"='Farley' WHERE "first_name"='Gregory'
58```
59
60**[`DialectWrapper.Update`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Update)**
61
62Use this when you want to create SQL for a specific `dialect`
63
64```go
65// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
66
67dialect := goqu.Dialect("mysql")
68
69ds := dialect.Update("user").Set(
70 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
71)
72updateSQL, _, _ := ds.ToSQL()
73fmt.Println(insertSQL, args)
74```
75Output:
76```
77UPDATE `user` SET `first_name`='Greg', `last_name`='Farley'
78```
79
80**[`Database.Update`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Update)**
81
82Use this when you want to execute the SQL or create SQL for the drivers dialect.
83
84```go
85// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
86
87mysqlDB := //initialize your db
88db := goqu.New("mysql", mysqlDB)
89
90ds := db.Update("user").Set(
91 goqu.Record{"first_name": "Greg", "last_name": "Farley"},
92)
93updateSQL, _, _ := ds.ToSQL()
94fmt.Println(insertSQL, args)
95```
96Output:
97```
98UPDATE `user` SET `first_name`='Greg', `last_name`='Farley'
99```
100
101### Examples
102
103For more examples visit the **[Docs](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset)**
104
105<a name="set-record"></a>
106**[Set with `goqu.Record`](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.Set)**
107
108```go
109sql, args, _ := goqu.Update("items").Set(
110 goqu.Record{"name": "Test", "address": "111 Test Addr"},
111).ToSQL()
112fmt.Println(sql, args)
113```
114
115Output:
116```
117UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
118```
119
120<a name="set-struct"></a>
121**[Set with Struct](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.Set)**
122
123```go
124type item struct {
125 Address string `db:"address"`
126 Name string `db:"name"`
127}
128sql, args, _ := goqu.Update("items").Set(
129 item{Name: "Test", Address: "111 Test Addr"},
130).ToSQL()
131fmt.Println(sql, args)
132```
133
134Output:
135```
136UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
137```
138
139With structs you can also skip fields by using the `skipupdate` tag
140
141```go
142type item struct {
143 Address string `db:"address"`
144 Name string `db:"name" goqu:"skipupdate"`
145}
146sql, args, _ := goqu.Update("items").Set(
147 item{Name: "Test", Address: "111 Test Addr"},
148).ToSQL()
149fmt.Println(sql, args)
150```
151
152Output:
153```
154UPDATE "items" SET "address"='111 Test Addr' []
155```
156
157If you want to use the database `DEFAULT` when the struct field is a zero value you can use the `defaultifempty` tag.
158
159```go
160type item struct {
161 Address string `db:"address"`
162 Name string `db:"name" goqu:"defaultifempty"`
163}
164sql, args, _ := goqu.Update("items").Set(
165 item{Address: "111 Test Addr"},
166).ToSQL()
167fmt.Println(sql, args)
168```
169
170Output:
171```
172UPDATE "items" SET "address"='111 Test Addr',"name"=DEFAULT []
173```
174
175`goqu` will also use fields in embedded structs when creating an update.
176
177**NOTE** unexported fields will be ignored!
178
179```go
180type Address struct {
181 Street string `db:"address_street"`
182 State string `db:"address_state"`
183}
184type User struct {
185 Address
186 FirstName string
187 LastName string
188}
189ds := goqu.Update("user").Set(
190 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
191)
192updateSQL, args, _ := ds.ToSQL()
193fmt.Println(updateSQL, args)
194```
195
196Output:
197```
198UPDATE "user" SET "address_state"='NY',"address_street"='111 Street',"firstname"='Greg',"lastname"='Farley' []
199```
200
201**NOTE** When working with embedded pointers if the embedded struct is nil then the fields will be ignored.
202
203```go
204type Address struct {
205 Street string
206 State string
207}
208type User struct {
209 *Address
210 FirstName string
211 LastName string
212}
213ds := goqu.Update("user").Set(
214 User{FirstName: "Greg", LastName: "Farley"},
215)
216updateSQL, args, _ := ds.ToSQL()
217fmt.Println(updateSQL, args)
218```
219
220Output:
221```
222UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []
223```
224
225You can ignore an embedded struct or struct pointer by using `db:"-"`
226
227```go
228type Address struct {
229 Street string
230 State string
231}
232type User struct {
233 Address `db:"-"`
234 FirstName string
235 LastName string
236}
237ds := goqu.Update("user").Set(
238 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
239)
240updateSQL, args, _ := ds.ToSQL()
241fmt.Println(updateSQL, args)
242```
243
244Output:
245```
246UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' []
247```
248
249
250<a name="set-map"></a>
251**[Set with Map](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.Set)**
252
253```go
254sql, args, _ := goqu.Update("items").Set(
255 map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
256).ToSQL()
257fmt.Println(sql, args)
258```
259
260Output:
261```
262UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
263```
264
265<a name="from"></a>
266**[From / Multi Table](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.From)**
267
268`goqu` allows joining multiple tables in a update clause through `From`.
269
270**NOTE** The `sqlite3` adapter does not support a multi table syntax.
271
272`Postgres` Example
273
274```go
275dialect := goqu.Dialect("postgres")
276
277ds := dialect.Update("table_one").
278 Set(goqu.Record{"foo": goqu.I("table_two.bar")}).
279 From("table_two").
280 Where(goqu.Ex{"table_one.id": goqu.I("table_two.id")})
281
282sql, _, _ := ds.ToSQL()
283fmt.Println(sql)
284```
285
286Output:
287```sql
288UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id")
289```
290
291`MySQL` Example
292
293```go
294dialect := goqu.Dialect("mysql")
295
296ds := dialect.Update("table_one").
297 Set(goqu.Record{"foo": goqu.I("table_two.bar")}).
298 From("table_two").
299 Where(goqu.Ex{"table_one.id": goqu.I("table_two.id")})
300
301sql, _, _ := ds.ToSQL()
302fmt.Println(sql)
303```
304Output:
305```sql
306UPDATE `table_one`,`table_two` SET `foo`=`table_two`.`bar` WHERE (`table_one`.`id` = `table_two`.`id`)
307```
308
309<a name="where"></a>
310**[Where](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.Where)**
311
312```go
313sql, _, _ := goqu.Update("test").
314 Set(goqu.Record{"foo": "bar"}).
315 Where(goqu.Ex{
316 "a": goqu.Op{"gt": 10},
317 "b": goqu.Op{"lt": 10},
318 "c": nil,
319 "d": []string{"a", "b", "c"},
320 }).ToSQL()
321fmt.Println(sql)
322```
323
324Output:
325```
326UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
327```
328
329<a name="order"></a>
330**[Order](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.Order)**
331
332**NOTE** This will only work if your dialect supports it
333
334```go
335// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
336
337ds := goqu.Dialect("mysql").
338 Update("test").
339 Set(goqu.Record{"foo": "bar"}).
340 Order(goqu.C("a").Asc())
341sql, _, _ := ds.ToSQL()
342fmt.Println(sql)
343```
344
345Output:
346```
347UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC
348```
349
350<a name="limit"></a>
351**[Order](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.Limit)**
352
353**NOTE** This will only work if your dialect supports it
354
355```go
356// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
357
358ds := goqu.Dialect("mysql").
359 Update("test").
360 Set(goqu.Record{"foo": "bar"}).
361 Limit(10)
362sql, _, _ := ds.ToSQL()
363fmt.Println(sql)
364```
365
366Output:
367```
368UPDATE `test` SET `foo`='bar' LIMIT 10
369```
370
371<a name="returning"></a>
372**[Returning](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.Returning)**
373
374Returning a single column example.
375
376```go
377sql, _, _ := goqu.Update("test").
378 Set(goqu.Record{"foo": "bar"}).
379 Returning("id").
380 ToSQL()
381fmt.Println(sql)
382```
383
384Output:
385```
386UPDATE "test" SET "foo"='bar' RETURNING "id"
387```
388
389Returning multiple columns
390
391```go
392sql, _, _ := goqu.Update("test").
393 Set(goqu.Record{"foo": "bar"}).
394 Returning("a", "b").
395 ToSQL()
396fmt.Println(sql)
397```
398
399Output:
400```
401UPDATE "test" SET "foo"='bar' RETURNING "a", "b"
402```
403
404Returning all columns
405
406```go
407sql, _, _ := goqu.Update("test").
408 Set(goqu.Record{"foo": "bar"}).
409 Returning(goqu.T("test").All()).
410 ToSQL()
411fmt.Println(sql)
412```
413
414Output:
415```
416UPDATE "test" SET "foo"='bar' RETURNING "test".*
417```
418
419<a name="seterror"></a>
420**[`SetError`](https://godoc.org/github.com/doug-martin/goqu/#UpdateDataset.SetError)**
421
422Sometimes while building up a query with goqu you will encounter situations where certain
423preconditions are not met or some end-user contraint has been violated. While you could
424track this error case separately, goqu provides a convenient built-in mechanism to set an
425error on a dataset if one has not already been set to simplify query building.
426
427Set an Error on a dataset:
428
429```go
430func GetUpdate(name string, value string) *goqu.UpdateDataset {
431
432 var ds = goqu.Update("test")
433
434 if len(name) == 0 {
435 return ds.SetError(fmt.Errorf("name is empty"))
436 }
437
438 if len(value) == 0 {
439 return ds.SetError(fmt.Errorf("value is empty"))
440 }
441
442 return ds.Set(goqu.Record{name: value})
443}
444
445```
446
447This error is returned on any subsequent call to `Error` or `ToSQL`:
448
449```go
450var field, value string
451ds = GetUpdate(field, value)
452fmt.Println(ds.Error())
453
454sql, args, err = ds.ToSQL()
455fmt.Println(err)
456```
457
458Output:
459```
460name is empty
461name is empty
462```
463
464<a name="executing"></a>
465## Executing Updates
466
467To execute Updates use [`goqu.Database#Update`](https://godoc.org/github.com/doug-martin/goqu/#Database.Update) to create your dataset
468
469### Examples
470
471**Executing an update**
472```go
473db := getDb()
474
475update := db.Update("goqu_user").
476 Where(goqu.C("first_name").Eq("Bob")).
477 Set(goqu.Record{"first_name": "Bobby"}).
478 Executor()
479
480if r, err := update.Exec(); err != nil {
481 fmt.Println(err.Error())
482} else {
483 c, _ := r.RowsAffected()
484 fmt.Printf("Updated %d users", c)
485}
486```
487
488Output:
489
490```
491Updated 1 users
492```
493
494**Executing with Returning**
495
496```go
497db := getDb()
498
499update := db.Update("goqu_user").
500 Set(goqu.Record{"last_name": "ucon"}).
501 Where(goqu.Ex{"last_name": "Yukon"}).
502 Returning("id").
503 Executor()
504
505var ids []int64
506if err := update.ScanVals(&ids); err != nil {
507 fmt.Println(err.Error())
508} else {
509 fmt.Printf("Updated users with ids %+v", ids)
510}
511
512```
513
514Output:
515```
516Updated users with ids [1 2 3]
517```
View as plain text