Skip to content
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

Open
ebadfd opened this issue Mar 16, 2023 · 2 comments
Open

Updating value when doing an bulk Upsert #6159

ebadfd opened this issue Mar 16, 2023 · 2 comments
Assignees
Labels
type:question general questions

Comments

@ebadfd
Copy link

ebadfd commented Mar 16, 2023

Your Question

this is related to the #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

Screenshot 2023-03-17 at 00 24 22

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 ?

@ebadfd ebadfd added the type:question general questions label Mar 16, 2023
@black-06
Copy link
Contributor

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

@aide-cloud
Copy link

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
Labels
type:question general questions
Projects
None yet
Development

No branches or pull requests

4 participants