-
-
Notifications
You must be signed in to change notification settings - Fork 3.9k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Updating value when doing an bulk Upsert #6159
Labels
type:question
general questions
Comments
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:
after:
|
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 This list of ids is wrong, and a large number of them do not exist in the database, like ghosts |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Your Question
this is related to the #3044 the bulk upsert does update the records but its not updating the correct values
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 ?
The text was updated successfully, but these errors were encountered: