1// nolint:lll // sql statements are long 2package goqu_test 3 4import ( 5 "fmt" 6 7 "github.com/doug-martin/goqu/v9" 8 _ "github.com/doug-martin/goqu/v9/dialect/mysql" 9) 10 11func ExampleUpdate_withStruct() { 12 type item struct { 13 Address string `db:"address"` 14 Name string `db:"name"` 15 } 16 sql, args, _ := goqu.Update("items").Set( 17 item{Name: "Test", Address: "111 Test Addr"}, 18 ).ToSQL() 19 fmt.Println(sql, args) 20 21 // Output: 22 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 23} 24 25func ExampleUpdate_withGoquRecord() { 26 sql, args, _ := goqu.Update("items").Set( 27 goqu.Record{"name": "Test", "address": "111 Test Addr"}, 28 ).ToSQL() 29 fmt.Println(sql, args) 30 31 // Output: 32 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 33} 34 35func ExampleUpdate_withMap() { 36 sql, args, _ := goqu.Update("items").Set( 37 map[string]interface{}{"name": "Test", "address": "111 Test Addr"}, 38 ).ToSQL() 39 fmt.Println(sql, args) 40 41 // Output: 42 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 43} 44 45func ExampleUpdate_withSkipUpdateTag() { 46 type item struct { 47 Address string `db:"address"` 48 Name string `db:"name" goqu:"skipupdate"` 49 } 50 sql, args, _ := goqu.Update("items").Set( 51 item{Name: "Test", Address: "111 Test Addr"}, 52 ).ToSQL() 53 fmt.Println(sql, args) 54 55 // Output: 56 // UPDATE "items" SET "address"='111 Test Addr' [] 57} 58 59func ExampleUpdateDataset_Executor() { 60 db := getDB() 61 update := db.Update("goqu_user"). 62 Where(goqu.C("first_name").Eq("Bob")). 63 Set(goqu.Record{"first_name": "Bobby"}). 64 Executor() 65 66 if r, err := update.Exec(); err != nil { 67 fmt.Println(err.Error()) 68 } else { 69 c, _ := r.RowsAffected() 70 fmt.Printf("Updated %d users", c) 71 } 72 73 // Output: 74 // Updated 1 users 75} 76 77func ExampleUpdateDataset_Executor_returning() { 78 db := getDB() 79 var ids []int64 80 update := db.Update("goqu_user"). 81 Set(goqu.Record{"last_name": "ucon"}). 82 Where(goqu.Ex{"last_name": "Yukon"}). 83 Returning("id"). 84 Executor() 85 if err := update.ScanVals(&ids); err != nil { 86 fmt.Println(err.Error()) 87 } else { 88 fmt.Printf("Updated users with ids %+v", ids) 89 } 90 91 // Output: 92 // Updated users with ids [1 2 3] 93} 94 95func ExampleUpdateDataset_Returning() { 96 sql, _, _ := goqu.Update("test"). 97 Set(goqu.Record{"foo": "bar"}). 98 Returning("id"). 99 ToSQL() 100 fmt.Println(sql) 101 sql, _, _ = goqu.Update("test"). 102 Set(goqu.Record{"foo": "bar"}). 103 Returning(goqu.T("test").All()). 104 ToSQL() 105 fmt.Println(sql) 106 sql, _, _ = goqu.Update("test"). 107 Set(goqu.Record{"foo": "bar"}). 108 Returning("a", "b"). 109 ToSQL() 110 fmt.Println(sql) 111 // Output: 112 // UPDATE "test" SET "foo"='bar' RETURNING "id" 113 // UPDATE "test" SET "foo"='bar' RETURNING "test".* 114 // UPDATE "test" SET "foo"='bar' RETURNING "a", "b" 115} 116 117func ExampleUpdateDataset_With() { 118 sql, _, _ := goqu.Update("test"). 119 With("some_vals(val)", goqu.From().Select(goqu.L("123"))). 120 Where(goqu.C("val").Eq(goqu.From("some_vals").Select("val"))). 121 Set(goqu.Record{"name": "Test"}).ToSQL() 122 fmt.Println(sql) 123 124 // Output: 125 // WITH some_vals(val) AS (SELECT 123) UPDATE "test" SET "name"='Test' WHERE ("val" IN (SELECT "val" FROM "some_vals")) 126} 127 128func ExampleUpdateDataset_WithRecursive() { 129 sql, _, _ := goqu.Update("nums"). 130 WithRecursive("nums(x)", goqu.From().Select(goqu.L("1").As("num")). 131 UnionAll(goqu.From("nums"). 132 Select(goqu.L("x+1").As("num")).Where(goqu.C("x").Lt(5)))). 133 Set(goqu.Record{"foo": goqu.T("nums").Col("num")}). 134 ToSQL() 135 fmt.Println(sql) 136 // Output: 137 // WITH RECURSIVE nums(x) AS (SELECT 1 AS "num" UNION ALL (SELECT x+1 AS "num" FROM "nums" WHERE ("x" < 5))) UPDATE "nums" SET "foo"="nums"."num" 138} 139 140func ExampleUpdateDataset_Limit() { 141 ds := goqu.Dialect("mysql"). 142 Update("test"). 143 Set(goqu.Record{"foo": "bar"}). 144 Limit(10) 145 sql, _, _ := ds.ToSQL() 146 fmt.Println(sql) 147 // Output: 148 // UPDATE `test` SET `foo`='bar' LIMIT 10 149} 150 151func ExampleUpdateDataset_LimitAll() { 152 ds := goqu.Dialect("mysql"). 153 Update("test"). 154 Set(goqu.Record{"foo": "bar"}). 155 LimitAll() 156 sql, _, _ := ds.ToSQL() 157 fmt.Println(sql) 158 // Output: 159 // UPDATE `test` SET `foo`='bar' LIMIT ALL 160} 161 162func ExampleUpdateDataset_ClearLimit() { 163 ds := goqu.Dialect("mysql"). 164 Update("test"). 165 Set(goqu.Record{"foo": "bar"}). 166 Limit(10) 167 sql, _, _ := ds.ClearLimit().ToSQL() 168 fmt.Println(sql) 169 // Output: 170 // UPDATE `test` SET `foo`='bar' 171} 172 173func ExampleUpdateDataset_Order() { 174 ds := goqu.Dialect("mysql"). 175 Update("test"). 176 Set(goqu.Record{"foo": "bar"}). 177 Order(goqu.C("a").Asc()) 178 sql, _, _ := ds.ToSQL() 179 fmt.Println(sql) 180 // Output: 181 // UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC 182} 183 184func ExampleUpdateDataset_OrderAppend() { 185 ds := goqu.Dialect("mysql"). 186 Update("test"). 187 Set(goqu.Record{"foo": "bar"}). 188 Order(goqu.C("a").Asc()) 189 sql, _, _ := ds.OrderAppend(goqu.C("b").Desc().NullsLast()).ToSQL() 190 fmt.Println(sql) 191 // Output: 192 // UPDATE `test` SET `foo`='bar' ORDER BY `a` ASC, `b` DESC NULLS LAST 193} 194 195func ExampleUpdateDataset_OrderPrepend() { 196 ds := goqu.Dialect("mysql"). 197 Update("test"). 198 Set(goqu.Record{"foo": "bar"}). 199 Order(goqu.C("a").Asc()) 200 201 sql, _, _ := ds.OrderPrepend(goqu.C("b").Desc().NullsLast()).ToSQL() 202 fmt.Println(sql) 203 // Output: 204 // UPDATE `test` SET `foo`='bar' ORDER BY `b` DESC NULLS LAST, `a` ASC 205} 206 207func ExampleUpdateDataset_ClearOrder() { 208 ds := goqu.Dialect("mysql"). 209 Update("test"). 210 Set(goqu.Record{"foo": "bar"}). 211 Order(goqu.C("a").Asc()) 212 sql, _, _ := ds.ClearOrder().ToSQL() 213 fmt.Println(sql) 214 // Output: 215 // UPDATE `test` SET `foo`='bar' 216} 217 218func ExampleUpdateDataset_From() { 219 ds := goqu.Update("table_one"). 220 Set(goqu.Record{"foo": goqu.I("table_two.bar")}). 221 From("table_two"). 222 Where(goqu.Ex{"table_one.id": goqu.I("table_two.id")}) 223 224 sql, _, _ := ds.ToSQL() 225 fmt.Println(sql) 226 // Output: 227 // UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id") 228} 229 230func ExampleUpdateDataset_From_postgres() { 231 dialect := goqu.Dialect("postgres") 232 233 ds := dialect.Update("table_one"). 234 Set(goqu.Record{"foo": goqu.I("table_two.bar")}). 235 From("table_two"). 236 Where(goqu.Ex{"table_one.id": goqu.I("table_two.id")}) 237 238 sql, _, _ := ds.ToSQL() 239 fmt.Println(sql) 240 // Output: 241 // UPDATE "table_one" SET "foo"="table_two"."bar" FROM "table_two" WHERE ("table_one"."id" = "table_two"."id") 242} 243 244func ExampleUpdateDataset_From_mysql() { 245 dialect := goqu.Dialect("mysql") 246 247 ds := dialect.Update("table_one"). 248 Set(goqu.Record{"foo": goqu.I("table_two.bar")}). 249 From("table_two"). 250 Where(goqu.Ex{"table_one.id": goqu.I("table_two.id")}) 251 252 sql, _, _ := ds.ToSQL() 253 fmt.Println(sql) 254 // Output: 255 // UPDATE `table_one`,`table_two` SET `foo`=`table_two`.`bar` WHERE (`table_one`.`id` = `table_two`.`id`) 256} 257 258func ExampleUpdateDataset_Where() { 259 // By default everything is anded together 260 sql, _, _ := goqu.Update("test"). 261 Set(goqu.Record{"foo": "bar"}). 262 Where(goqu.Ex{ 263 "a": goqu.Op{"gt": 10}, 264 "b": goqu.Op{"lt": 10}, 265 "c": nil, 266 "d": []string{"a", "b", "c"}, 267 }).ToSQL() 268 fmt.Println(sql) 269 // You can use ExOr to get ORed expressions together 270 sql, _, _ = goqu.Update("test"). 271 Set(goqu.Record{"foo": "bar"}). 272 Where(goqu.ExOr{ 273 "a": goqu.Op{"gt": 10}, 274 "b": goqu.Op{"lt": 10}, 275 "c": nil, 276 "d": []string{"a", "b", "c"}, 277 }).ToSQL() 278 fmt.Println(sql) 279 // You can use Or with Ex to Or multiple Ex maps together 280 sql, _, _ = goqu.Update("test"). 281 Set(goqu.Record{"foo": "bar"}). 282 Where( 283 goqu.Or( 284 goqu.Ex{ 285 "a": goqu.Op{"gt": 10}, 286 "b": goqu.Op{"lt": 10}, 287 }, 288 goqu.Ex{ 289 "c": nil, 290 "d": []string{"a", "b", "c"}, 291 }, 292 ), 293 ).ToSQL() 294 fmt.Println(sql) 295 // By default everything is anded together 296 sql, _, _ = goqu.Update("test"). 297 Set(goqu.Record{"foo": "bar"}). 298 Where( 299 goqu.C("a").Gt(10), 300 goqu.C("b").Lt(10), 301 goqu.C("c").IsNull(), 302 goqu.C("d").In("a", "b", "c"), 303 ).ToSQL() 304 fmt.Println(sql) 305 // You can use a combination of Ors and Ands 306 sql, _, _ = goqu.Update("test"). 307 Set(goqu.Record{"foo": "bar"}). 308 Where( 309 goqu.Or( 310 goqu.C("a").Gt(10), 311 goqu.And( 312 goqu.C("b").Lt(10), 313 goqu.C("c").IsNull(), 314 ), 315 ), 316 ).ToSQL() 317 fmt.Println(sql) 318 // Output: 319 // UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))) 320 // UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c'))) 321 // UPDATE "test" SET "foo"='bar' WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))) 322 // UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))) 323 // UPDATE "test" SET "foo"='bar' WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL))) 324} 325 326func ExampleUpdateDataset_Where_prepared() { 327 // By default everything is anded together 328 sql, args, _ := goqu.Update("test"). 329 Prepared(true). 330 Set(goqu.Record{"foo": "bar"}). 331 Where(goqu.Ex{ 332 "a": goqu.Op{"gt": 10}, 333 "b": goqu.Op{"lt": 10}, 334 "c": nil, 335 "d": []string{"a", "b", "c"}, 336 }).ToSQL() 337 fmt.Println(sql, args) 338 // You can use ExOr to get ORed expressions together 339 sql, args, _ = goqu.Update("test").Prepared(true). 340 Set(goqu.Record{"foo": "bar"}). 341 Where(goqu.ExOr{ 342 "a": goqu.Op{"gt": 10}, 343 "b": goqu.Op{"lt": 10}, 344 "c": nil, 345 "d": []string{"a", "b", "c"}, 346 }).ToSQL() 347 fmt.Println(sql, args) 348 // You can use Or with Ex to Or multiple Ex maps together 349 sql, args, _ = goqu.Update("test").Prepared(true). 350 Set(goqu.Record{"foo": "bar"}). 351 Where( 352 goqu.Or( 353 goqu.Ex{ 354 "a": goqu.Op{"gt": 10}, 355 "b": goqu.Op{"lt": 10}, 356 }, 357 goqu.Ex{ 358 "c": nil, 359 "d": []string{"a", "b", "c"}, 360 }, 361 ), 362 ).ToSQL() 363 fmt.Println(sql, args) 364 // By default everything is anded together 365 sql, args, _ = goqu.Update("test").Prepared(true). 366 Set(goqu.Record{"foo": "bar"}). 367 Where( 368 goqu.C("a").Gt(10), 369 goqu.C("b").Lt(10), 370 goqu.C("c").IsNull(), 371 goqu.C("d").In("a", "b", "c"), 372 ).ToSQL() 373 fmt.Println(sql, args) 374 // You can use a combination of Ors and Ands 375 sql, args, _ = goqu.Update("test").Prepared(true). 376 Set(goqu.Record{"foo": "bar"}). 377 Where( 378 goqu.Or( 379 goqu.C("a").Gt(10), 380 goqu.And( 381 goqu.C("b").Lt(10), 382 goqu.C("c").IsNull(), 383 ), 384 ), 385 ).ToSQL() 386 fmt.Println(sql, args) 387 // Output: 388 // UPDATE "test" SET "foo"=? WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [bar 10 10 a b c] 389 // UPDATE "test" SET "foo"=? WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [bar 10 10 a b c] 390 // UPDATE "test" SET "foo"=? WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [bar 10 10 a b c] 391 // UPDATE "test" SET "foo"=? WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [bar 10 10 a b c] 392 // UPDATE "test" SET "foo"=? WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [bar 10 10] 393} 394 395func ExampleUpdateDataset_ClearWhere() { 396 ds := goqu. 397 Update("test"). 398 Set(goqu.Record{"foo": "bar"}). 399 Where( 400 goqu.Or( 401 goqu.C("a").Gt(10), 402 goqu.And( 403 goqu.C("b").Lt(10), 404 goqu.C("c").IsNull(), 405 ), 406 ), 407 ) 408 sql, _, _ := ds.ClearWhere().ToSQL() 409 fmt.Println(sql) 410 // Output: 411 // UPDATE "test" SET "foo"='bar' 412} 413 414func ExampleUpdateDataset_Table() { 415 ds := goqu.Update("test") 416 sql, _, _ := ds.Table("test2").Set(goqu.Record{"foo": "bar"}).ToSQL() 417 fmt.Println(sql) 418 // Output: 419 // UPDATE "test2" SET "foo"='bar' 420} 421 422func ExampleUpdateDataset_Table_aliased() { 423 ds := goqu.Update("test") 424 sql, _, _ := ds.Table(goqu.T("test").As("t")).Set(goqu.Record{"foo": "bar"}).ToSQL() 425 fmt.Println(sql) 426 // Output: 427 // UPDATE "test" AS "t" SET "foo"='bar' 428} 429 430func ExampleUpdateDataset_Set() { 431 type item struct { 432 Address string `db:"address"` 433 Name string `db:"name"` 434 } 435 sql, args, _ := goqu.Update("items").Set( 436 item{Name: "Test", Address: "111 Test Addr"}, 437 ).ToSQL() 438 fmt.Println(sql, args) 439 440 sql, args, _ = goqu.Update("items").Set( 441 goqu.Record{"name": "Test", "address": "111 Test Addr"}, 442 ).ToSQL() 443 fmt.Println(sql, args) 444 445 sql, args, _ = goqu.Update("items").Set( 446 map[string]interface{}{"name": "Test", "address": "111 Test Addr"}, 447 ).ToSQL() 448 fmt.Println(sql, args) 449 450 // Output: 451 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 452 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 453 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 454} 455 456func ExampleUpdateDataset_Set_struct() { 457 type item struct { 458 Address string `db:"address"` 459 Name string `db:"name"` 460 } 461 sql, args, _ := goqu.Update("items").Set( 462 item{Name: "Test", Address: "111 Test Addr"}, 463 ).ToSQL() 464 fmt.Println(sql, args) 465 466 // Output: 467 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 468} 469 470func ExampleUpdateDataset_Set_goquRecord() { 471 sql, args, _ := goqu.Update("items").Set( 472 goqu.Record{"name": "Test", "address": "111 Test Addr"}, 473 ).ToSQL() 474 fmt.Println(sql, args) 475 476 // Output: 477 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 478} 479 480func ExampleUpdateDataset_Set_map() { 481 sql, args, _ := goqu.Update("items").Set( 482 map[string]interface{}{"name": "Test", "address": "111 Test Addr"}, 483 ).ToSQL() 484 fmt.Println(sql, args) 485 486 // Output: 487 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 488} 489 490func ExampleUpdateDataset_Set_withSkipUpdateTag() { 491 type item struct { 492 Address string `db:"address"` 493 Name string `db:"name" goqu:"skipupdate"` 494 } 495 sql, args, _ := goqu.Update("items").Set( 496 item{Name: "Test", Address: "111 Test Addr"}, 497 ).ToSQL() 498 fmt.Println(sql, args) 499 500 // Output: 501 // UPDATE "items" SET "address"='111 Test Addr' [] 502} 503 504func ExampleUpdateDataset_Set_withDefaultIfEmptyTag() { 505 type item struct { 506 Address string `db:"address"` 507 Name string `db:"name" goqu:"defaultifempty"` 508 } 509 sql, args, _ := goqu.Update("items").Set( 510 item{Address: "111 Test Addr"}, 511 ).ToSQL() 512 fmt.Println(sql, args) 513 514 sql, args, _ = goqu.Update("items").Set( 515 item{Name: "Bob Yukon", Address: "111 Test Addr"}, 516 ).ToSQL() 517 fmt.Println(sql, args) 518 519 // Output: 520 // UPDATE "items" SET "address"='111 Test Addr',"name"=DEFAULT [] 521 // UPDATE "items" SET "address"='111 Test Addr',"name"='Bob Yukon' [] 522} 523 524func ExampleUpdateDataset_Set_withNoTags() { 525 type item struct { 526 Address string 527 Name string 528 } 529 sql, args, _ := goqu.Update("items").Set( 530 item{Name: "Test", Address: "111 Test Addr"}, 531 ).ToSQL() 532 fmt.Println(sql, args) 533 534 // Output: 535 // UPDATE "items" SET "address"='111 Test Addr',"name"='Test' [] 536} 537 538func ExampleUpdateDataset_Set_withEmbeddedStruct() { 539 type Address struct { 540 Street string `db:"address_street"` 541 State string `db:"address_state"` 542 } 543 type User struct { 544 Address 545 FirstName string 546 LastName string 547 } 548 ds := goqu.Update("user").Set( 549 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"}, 550 ) 551 updateSQL, args, _ := ds.ToSQL() 552 fmt.Println(updateSQL, args) 553 554 // Output: 555 // UPDATE "user" SET "address_state"='NY',"address_street"='111 Street',"firstname"='Greg',"lastname"='Farley' [] 556} 557 558func ExampleUpdateDataset_Set_withIgnoredEmbedded() { 559 type Address struct { 560 Street string 561 State string 562 } 563 type User struct { 564 Address `db:"-"` 565 FirstName string 566 LastName string 567 } 568 ds := goqu.Update("user").Set( 569 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"}, 570 ) 571 updateSQL, args, _ := ds.ToSQL() 572 fmt.Println(updateSQL, args) 573 574 // Output: 575 // UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' [] 576} 577 578func ExampleUpdateDataset_Set_withNilEmbeddedPointer() { 579 type Address struct { 580 Street string 581 State string 582 } 583 type User struct { 584 *Address 585 FirstName string 586 LastName string 587 } 588 ds := goqu.Update("user").Set( 589 User{FirstName: "Greg", LastName: "Farley"}, 590 ) 591 updateSQL, args, _ := ds.ToSQL() 592 fmt.Println(updateSQL, args) 593 594 // Output: 595 // UPDATE "user" SET "firstname"='Greg',"lastname"='Farley' [] 596} 597 598func ExampleUpdateDataset_ToSQL_prepared() { 599 type item struct { 600 Address string `db:"address"` 601 Name string `db:"name"` 602 } 603 604 sql, args, _ := goqu.From("items").Prepared(true).Update().Set( 605 item{Name: "Test", Address: "111 Test Addr"}, 606 ).ToSQL() 607 fmt.Println(sql, args) 608 609 sql, args, _ = goqu.From("items").Prepared(true).Update().Set( 610 goqu.Record{"name": "Test", "address": "111 Test Addr"}, 611 ).ToSQL() 612 fmt.Println(sql, args) 613 614 sql, args, _ = goqu.From("items").Prepared(true).Update().Set( 615 map[string]interface{}{"name": "Test", "address": "111 Test Addr"}, 616 ).ToSQL() 617 fmt.Println(sql, args) 618 // Output: 619 // UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test] 620 // UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test] 621 // UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test] 622} 623 624func ExampleUpdateDataset_Prepared() { 625 sql, args, _ := goqu.Update("items").Prepared(true).Set( 626 goqu.Record{"name": "Test", "address": "111 Test Addr"}, 627 ).ToSQL() 628 fmt.Println(sql, args) 629 630 // Output: 631 // UPDATE "items" SET "address"=?,"name"=? [111 Test Addr Test] 632} 633