Page History
...
UNION ALL can be used to combine the result sets of two or more SELECT statements and will include duplicate rows.
Example
| Code Block |
|---|
SELECT dodad
FROM newproduct
UNION ALL
SELECT dodad
FROM newproductclone
GO |
INTERSECT
INTERSECT returns the results of two or more SELECT statements. However, it only returns the rows retrieved by all SELECTs. If a record exists in one statement and not in the other, it will be omitted from the INTERSECT results.
Example
| Code Block |
|---|
SELECT customerid
FROM customers
INTERSECT
SELECT customerid
FROM orders
GO |
| Info |
|---|
INTERSECT clauses can be very CPU and RAM intensive because the resulting documents are compared and selected on the client side within Aqua Data Studio. Out of Memory Exceptions can occur if extremely large amounts of documents are compared and selected. See our Memory Configuration page on how to increase the RAM Aqua Data Studio uses. |
| Info |
|---|
Nested UNION, EXCEPT, and INTERSECT are not supported. |
EXCEPT
EXCEPT combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. EXCEPT returns only rows not available in second SELECT statement.
Example
| Code Block |
|---|
SELECT customerid
FROM customers
EXCEPT
SELECT customerid
FROM orders
GO |
| Info |
|---|
EXCEPT clauses can be very CPU and RAM intensive because the resulting documents are compared and selected on the client side within Aqua Data Studio. Out of Memory Exceptions can occur if extremely large amounts of documents are compared and selected. See our Memory Configuration page on how to increase the RAM Aqua Data Studio uses. |
| Info |
|---|
Nested UNION, EXCEPT, and INTERSECT are not supported. |
DML Syntax
Data manipulation language (DML) is a family of syntax elements used to insert, delete and update data in a database. The DML below is specific to MongoDB in Aqua Data Studio.
...
INSERT places data into a specified Collection. Data can be specified in JSON format or as values. If an INSERT includes a field that does not already exist in a collection, that field will be created.
| Info |
|---|
The Fields list displayed within Aqua Data Studio's Schema Browser for a collection is generated by examining the first document in its collection. After executing an INSERT that includes new fields for a collection, the Schema Browser may not display these new fields. By examining the _id value next to the Fields node in the Schema Browser you can determine which document is being used to generate the Fields list. |
| Info |
|---|
When using the INSERT ... SELECT syntax on a sub-document field, you need to pass in the top-level field in JSON format. For example: |
Examples
| Code Block |
|---|
INSERT INTO zips
VALUES ({"city": "MEER", "loc": [-86.51599, 33.584199], "pop": 6055, "state": "CA", "_id": "99999"}) |
| Code Block |
|---|
INSERT INTO zips (city)
VALUES ('CRANKTON') |
| Code Block |
|---|
INSERT INTO cities (name)
SELECT city FROM zips |
...
Default is UPDATE ALL. UPDATE ONE updates only the first document that matches the criteria. UPDATE updates the contents of a Collection.
| Info |
|---|
If the field name doesn't exist in the collection, it will be created. The default behavior of a MongoSQL UPDATE statement updates all records that satisfy the WHERE criteria. If there is no WHERE clause, then all records will be updated. This does function differently than the default behavior of MongoDB's update operation, which updates the first document that matches the criteria. |
Example
| Code Block |
|---|
UPDATE zips SET pop='626'
WHERE city='CANKTON'
AND state='LA' |
...
Default is UPSERT ALL. If a document that satisfies the WHERE criteria is found, UPSERT updates every field inside the matched document with its corresponding value from the given document.
| Info |
|---|
The Fields list displayed within Aqua Data Studio's Schema Browser for a collection is generated by examining the first document in its collection. After executing a REPLACE INTO that includes fields which are new to the collection, the Schema Browser may not display these new fields. By examining the _id value next to the Fields node in the Schema Browser you can determine which document is being used to generate the Fields list. |
Example
REPLACE INTO zips
VALUE({"city": "GRONTOWN", "loc": [-92.533765, 30.025565], "pop": 83, "state": "LA"})
WHERE city='GRONTOWN'...
SELECT * FROM NewProducts WHERE ARRAY_SIZE(city) = 12
INTERSECT clauses can be very CPU and RAM intensive because the resulting documents are compared and selected on the client side within Aqua Data Studio. Out of Memory Exceptions can occur if extremely large amounts of documents are compared and selected. See our Memory Configuration page on how to increase the RAM Aqua Data Studio uses.