Your Question
this is related to the https://github.com/go-gorm/gorm/issues/3044 the bulk upsert does update the records but its not updating the correct values
if err := tx.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "key"}, {Name: "user_id"}},
DoUpdates: clause.Assignments(map[string]interface{}{"value": meta}),
}).Create(meta).Table("meta").Error; err != nil {
return err
}
the thing is this actually updates multiple values like here my meta object is a array with multiple keys and user_ids so if i run this it will update all 3 records but cant seems to get the values from meta.value it just saves data as a array
any way to fix this ? right now im running an loop and extracting value to save
The document you expected this should be explained
The document link you expected this question should be explained in our Clauses
Expected answer
is there any way to do this ?
Comment From: black-06
type Meta struct {
Key string `gorm:"index:uq_id,unique"`
UserID string `gorm:"index:uq_id,unique"`
Value string
Other string
}
// old values
db.Create(&[]Meta{
{Key: "height", UserID: "user1", Value: "old 1", Other: "other 1"},
{Key: "weight", UserID: "user1", Value: "old 2", Other: "other 2"},
{Key: "bmi", UserID: "user1", Value: "old 3", Other: "other 3"},
})
// new values
meta := []Meta{
{Key: "height", UserID: "user1", Value: "new 1", Other: "do not update"},
{Key: "weight", UserID: "user1", Value: "new 2", Other: "do not update"},
{Key: "bmi", UserID: "user1", Value: "new 3", Other: "do not update"},
}
// upsert (only "value" column)
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "key"}, {Name: "user_id"}},
DoUpdates: clause.AssignmentColumns([]string{"value"}),
}).Create(&meta)
before: | key | user_id | value | other | |--------|---------|-------|---------| | height | user1 | old 1 | other 1 | | weight | user1 | old 2 | other 2 | | bmi | user1 | old 3 | other 3 |
after: | key | user_id | value | other | |--------|---------|-------|---------| | height | user1 | new 1 | other 1 | | weight | user1 | new 2 | other 2 | | bmi | user1 | new 3 | other 3 |
Comment From: aide-cloud
I ran into a similar problem with my code and the corresponding sql generated
err := db.Transaction(func(tx *gorm.DB) error {
if err := tx.Model(&InventoryDetail{}).Clauses(clause.OnConflict{
UpdateAll: true,
DoUpdates: clause.AssignmentColumns([]string{"good_id", "inventory_id", "put_in_count", "price", "amount", "remark", "updated_at", "deleted_at", "depository_id"}),
}).CreateInBatches(&insertVal, 500).Error; err != nil {
return err
}
newIds := make([]uint, 0)
for _, v := range insertVal {
newIds = append(newIds, v.ID)
}
fmt.Println(oldIDs)
fmt.Println(newIds)
if err := tx.Model(&InventoryDetail{}).Where("id not in ?", newIds).Delete(&InventoryDetail{}).Error; err != nil {
return err
}
return nil
})
insert
[0m[33m[8.087ms] [34;1m[rows:95][0m INSERT INTO `inventory_details` (`eid`,`created_at`,`updated_at`,`deleted_at`,`project_id`,`inventory_id`,`good_id`,`depository_id`,`order_id`,`out_count`,`amount`,`from`,`remark`,`put_in_count`,`price`) VALUES ('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73346,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73345,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73344,213,0,0,'',1,'',40000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73343,213,0,0,'',1,'',120000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73342,213,0,0,'',1,'',80000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73341,213,0,0,'',1,'',120000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73340,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73339,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73338,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73337,213,0,0,'',1,'',30000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73336,213,0,0,'',1,'',160000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73335,213,0,0,'',1,'',70000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73334,213,0,0,'',1,'',80000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73333,213,0,0,'',1,'',170000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73332,213,0,0,'',1,'',90000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73331,213,0,0,'',1,'',310000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73330,213,0,0,'',1,'',120000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73329,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73328,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73327,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73326,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73325,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73324,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73323,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73322,213,0,0,'',1,'',50000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73321,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73320,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73319,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73318,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73317,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73316,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73315,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73314,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73313,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73312,213,0,0,'',1,'',60000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73311,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73310,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73309,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73308,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73307,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73306,213,0,0,'',1,'',50000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73305,213,0,0,'',1,'',50000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73304,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73303,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73302,213,0,0,'',1,'',50000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73301,213,0,0,'',1,'',30000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73300,213,0,0,'',1,'',30000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73299,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73298,213,0,0,'',1,'',60000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73297,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73296,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73295,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73294,213,0,0,'',1,'',10000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73293,213,0,0,'',1,'',20000,0),('Af4e7ea2cd30d00566877f15f0b148521','2023-05-09 09:38:40.239','2023-05-09 09:38:40.239','0',8813884,4327,73292,213,0,0,'',1,'',10000,0) ON DUPLICATE KEY UPDATE `put_in_count`=VALUES(`put_in_count`),`price`=VALUES(`price`),`amount`=VALUES(`amount`),`remark`=VALUES(`remark`),`updated_at`=VALUES(`updated_at`),`deleted_at`=VALUES(`deleted_at`),`depository_id`=VALUES(`depository_id`),`updated_at`='2023-05-09 09:38:40.239',`deleted_at`=VALUES(`deleted_at`),`project_id`=VALUES(`project_id`),`inventory_id`=VALUES(`inventory_id`),`good_id`=VALUES(`good_id`),`depository_id`=VALUES(`depository_id`),`order_id`=VALUES(`order_id`),`out_count`=VALUES(`out_count`),`amount`=VALUES(`amount`),`from`=VALUES(`from`),`remark`=VALUES(`remark`)
delete
[0m[33m[5.196ms] [34;1m[rows:40][0m UPDATE `inventory_details` SET `deleted_at`=1683596320 WHERE `project_id` = 8813884 AND inventory_id = 4327 AND id not in (26261,26262,26263,26264,26265,26266,26267,26268,26269,26270,26271,26272,26273,26274,26275,26276,26277,26278,26279,26280,26281,26282,26283,26284,26285,26286,26287,26288,26289,26290,26291,26292,26293,26294,26295,26296,26297,26298,26299,26300,26301,26302,26303,26304,26305,26306,26307,26308,26309,26310,26311,26312,26313,26314,26315) AND `inventory_details`.`eid` = 'Af4e7ea2cd30d00566877f15f0b148521' AND `inventory_details`.`deleted_at` = 0