Difference between revisions of "MongoDB Update"

From mi-linux
Jump to navigationJump to search
 
(28 intermediate revisions by the same user not shown)
Line 5: Line 5:
 
The format of the update command is:
 
The format of the update command is:
  
<pre style="color:blue">
+
<pre style="color:purple">
 
  db.collectionName.update({'keyField': 'value' },  
 
  db.collectionName.update({'keyField': 'value' },  
 
   {$set: field: 'newValue' }
 
   {$set: field: 'newValue' }
Line 13: Line 13:
 
The '''update()''' function can be used to update one or more documents. If the change should only apply to one document, the keyField needs to be a field with unique values, to ensure the correct document is updated.
 
The '''update()''' function can be used to update one or more documents. If the change should only apply to one document, the keyField needs to be a field with unique values, to ensure the correct document is updated.
  
This is similar in SQL to proving the WHERE clause of an UPDATE command.
+
This is similar in SQL to providng the WHERE clause of an UPDATE command.
  
 
Alternatively since version 3.2, MongoDB also supports the following functions:
 
Alternatively since version 3.2, MongoDB also supports the following functions:
  
<pre style="color:blue">
+
<pre style="color:purple">
 
db.collectionName.updateOne(); /* updates a single document that matches a specified filter  
 
db.collectionName.updateOne(); /* updates a single document that matches a specified filter  
 
                                   (even if several documents match the filter) */
 
                                   (even if several documents match the filter) */
 
</pre>
 
</pre>
<pre style="color:blue">
+
<pre style="color:purple">
 
db.collectionName.updateMany(); /* updates all documents that matches a specified filter */
 
db.collectionName.updateMany(); /* updates all documents that matches a specified filter */
 
</pre>
 
</pre>
<pre style="color:blue">
+
<pre style="color:purple">
db.collectionName.replaceMany(); /* replaces a single document that matches a specified filter             
+
db.collectionName.replaceOne(); /* replaces the first document that matches a specified filter             
 
                                     (even if several documents match the filter) */
 
                                     (even if several documents match the filter) */
 
</pre>
 
</pre>
Line 31: Line 31:
  
  
=== Updating Department 40 ===
+
=== Updating the dept collection ===
  
To update department 40 to add an employee:
+
Update department 40 to change the location to Wolverhampton:
  
  db.deptCollection.update({'deptno':40},  
+
  db.dept.update({'deptno':40},  
   {$set:  
+
   {$set: {'loc': 'WOLVERHAMPTON'}})
    {'employees':
+
 
      {
+
Check the changes have been made:
      empno: 8888,
+
 
      ename: 'MARY',
+
db.dept.find({"deptno":40}).pretty()
      job: 'LECTURER',
+
 
      mgr: 7566,
+
=== Updating the emp collection ===
      hiredate: new Date(),
+
 
      sal: 4000
+
Carter has now become an analyst:
      }
+
 
    ]}
+
db.emp.update({'empno':7782},  
  }
+
{$set:  {job: 'ANALYST'}})
  )
+
 
 +
 
 +
This is equivalent to the SQL statement:
 +
 
 +
<pre style="color:purple">
 +
UPDATE emp SET job = 'ANALYST'
 +
WHERE empno = 7782;
 +
</pre>
 +
 
 +
 
 +
There are some limitations currently if you want to use a value in a field to update the value in another field, or even in the same field, such as increase existing salaries by 10%.
 +
 
 +
There are some field update operators that can be used, such as ''$inc'', which increments a field by a specified value. The ''$inc'' operator accepts positive and negative values.
 +
 
 +
For example, observe what the following does:
 +
 
 +
db.emp.update({},  
 +
{$inc:  {sal: 100}})
 +
 
 +
 
 +
The empty curly brackets this time means there is no equivalent of an SQL WHERE clause:
 +
 
 +
<pre style="color:purple">
 +
UPDATE emp SET sal = sal +100;
 +
</pre>
 +
 
 +
So you might think this should update all the salaries in all the documents by £100. Check the collection - is this the case?
 +
 
 +
  db.emp.find().pretty()
  
Change the location to Wolverhampton:
+
You should find only the first document has been updated.
  
db.deptCollection.update({'deptno':40},
+
''$inc'' can be used to decrease values too by using an negative number. To reduce the sal by £100:
  {$set:  {'loc': 'WOLVERHAMPTON'}})
 
  
Check the changes have been made:
+
db.emp.update({},
 +
  {$inc: {sal: -100}})
  
db.deptCollection.find({"deptno":40}).pretty()
+
This should reverse the previous increase.
  
 +
To update all the documents, ''updateMany()'' must be used instead:
  
The salary should have been 3000. There is an issue with the current design, in that the whole array has to be replaced:
+
db.emp.updateMany({},  
 +
  {$inc:  {sal: 100}})
  
  
db.deptCollection.update({'deptno':40},
+
Check the documents again and see if the salaries have now been increased by £100.
  {$set:
 
    {'employees': [ 
 
      {
 
      empno: 8888,
 
      ename: 'MARY',
 
      job: 'LECTURER',
 
      mgr: 7566,
 
      hiredate: new Date(),
 
      sal: 3000
 
      }
 
    ]}
 
  }
 
)
 
  
== Exercise 2.2 ==
+
== Exercise 2.4 ==
  
* 2.2.1 Update the name of department 40 to: COMPUTING
+
* 2.4.1 Update the name of department 40 to: COMPUTING
* 2.2.1 Update the ename of employee number xxx to xxx
+
* 2.4.1 Update the salary of employee number 7788 in department 20 to 3500
  
 
== Next Step ==
 
== Next Step ==
  
[[MongoDB_Delete | Deleting]] a document.
+
[[MongoDB_Delete | Deleting]] a document, or collection.

Latest revision as of 11:43, 13 November 2017

Main Page >> MongoDB >>MongoDB Workbook >> Updating Collections

Updating a Collection

The format of the update command is:

 db.collectionName.update({'keyField': 'value' }, 
  {$set: field: 'newValue' }
 )

The update() function can be used to update one or more documents. If the change should only apply to one document, the keyField needs to be a field with unique values, to ensure the correct document is updated.

This is similar in SQL to providng the WHERE clause of an UPDATE command.

Alternatively since version 3.2, MongoDB also supports the following functions:

db.collectionName.updateOne(); /* updates a single document that matches a specified filter 
                                  (even if several documents match the filter) */
db.collectionName.updateMany(); /* updates all documents that matches a specified filter */
db.collectionName.replaceOne(); /* replaces the first document that matches a specified filter             
                                    (even if several documents match the filter) */


Updating the dept collection

Update department 40 to change the location to Wolverhampton:

db.dept.update({'deptno':40}, 
 {$set:  {'loc': 'WOLVERHAMPTON'}})

Check the changes have been made:

db.dept.find({"deptno":40}).pretty()

Updating the emp collection

Carter has now become an analyst:

db.emp.update({'empno':7782}, 
{$set:  {job: 'ANALYST'}})


This is equivalent to the SQL statement:

UPDATE emp SET job = 'ANALYST'
WHERE empno = 7782;


There are some limitations currently if you want to use a value in a field to update the value in another field, or even in the same field, such as increase existing salaries by 10%.

There are some field update operators that can be used, such as $inc, which increments a field by a specified value. The $inc operator accepts positive and negative values.

For example, observe what the following does:

db.emp.update({}, 
{$inc:  {sal: 100}})


The empty curly brackets this time means there is no equivalent of an SQL WHERE clause:

UPDATE emp SET sal = sal +100;

So you might think this should update all the salaries in all the documents by £100. Check the collection - is this the case?

db.emp.find().pretty()

You should find only the first document has been updated.

$inc can be used to decrease values too by using an negative number. To reduce the sal by £100:

db.emp.update({}, 
 {$inc:  {sal: -100}})

This should reverse the previous increase.

To update all the documents, updateMany() must be used instead:

db.emp.updateMany({}, 
 {$inc:  {sal: 100}})


Check the documents again and see if the salaries have now been increased by £100.

Exercise 2.4

  • 2.4.1 Update the name of department 40 to: COMPUTING
  • 2.4.1 Update the salary of employee number 7788 in department 20 to 3500

Next Step

Deleting a document, or collection.