MongoDB: insert on duplicate key update

Question

How can do this with Mongo in one [atomic] statement:

insert mytable set MyUniqueKey = ?, X = ? on duplicate key update Y = ?

When this statement is executed for first time it will set X value but after that it will only update Y value.

Only MyUniqueKey is part of unique key and should be looked up for duplicates.

score 16 · Answer 1 · edited Feb 22 '14 at 21:51

16

You are looking for the upsert option on the Update command. The docs should be enough here.

edited Feb 22 '14 at 21:51

superhero

6,281
11
59
91

answered Apr 08 '12 at 04:14

Gates VP

44,957
11
105
108

2

would you please give an example. I have already tried many combination of options, but I can't select a field to be set on insert but be excluded on update. – Ali Shakiba Apr 08 '12 at 04:35
there is a field I like to be set only on first insert but remain un-touched on updates. – Ali Shakiba Apr 08 '12 at 04:37
You have to make `Y` part of the `query` portion of the update instead of part of the `update` portion. – Gates VP Apr 08 '12 at 05:11

score 5 · Accepted Answer · answered May 17 '12 at 09:07

5

I asked this question on mongodb user group, the answer was that it is not possible and this is an open issue.

answered May 17 '12 at 09:07

Ali Shakiba

20,549
18
61
88

4

This issue has been closed in MongoDB 2.4 and you can now use [`$setOnInsert`](http://docs.mongodb.org/manual/reference/operator/setOnInsert/). – str Oct 03 '13 at 15:10

score 5 · Answer 3 · answered Jan 17 '17 at 13:33

From

$setOnInsert

I would call it "MyCollection" instead of "mytable". You can do the following:

db.mycollection.update(
   { MyUniqueKey: "?" },
   {  
      $set: {"y": "?"},
      $setOnInsert: {
          MyUniqueKey: "?",
           "x": "?",
   },
   { upsert: true }
)

Basically the $set runs always, except when document does not exist. Then $setOnInsert will run.

MongoDB: insert on duplicate key update

3 Answers3

Linked