--- title: "Updating data | SurrealDB University" description: "Updating data. A chapter of SurrealDB Fundamentals, a hands-on course with runnable examples." url: https://surrealdb.com/learn/fundamentals/schemaless/updating-data --- [Back to Courses](https://surrealdb.com/learn) Course chapters [SurrealDB Fundamentals](https://surrealdb.com/learn/fundamentals) [Introduction](https://surrealdb.com/learn/fundamentals) [Welcome to SurrealDB University](https://surrealdb.com/learn/fundamentals/intro/welcome) [Intro to SurrealDB](https://surrealdb.com/learn/fundamentals/intro/surrealdb) [Why SurrealQL is SQL-like](https://surrealdb.com/learn/fundamentals/intro/surrealql) [Part 1: Schemaless CRUD](https://surrealdb.com/learn/fundamentals/schemaless) [Introduction](https://surrealdb.com/learn/fundamentals/schemaless) [Record IDs](https://surrealdb.com/learn/fundamentals/schemaless/record-ids) [Inserting data](https://surrealdb.com/learn/fundamentals/schemaless/inserting-data) [Reading data](https://surrealdb.com/learn/fundamentals/schemaless/reading-data) [Updating data](https://surrealdb.com/learn/fundamentals/schemaless/updating-data) [Deleting data](https://surrealdb.com/learn/fundamentals/schemaless/deleting-data) [Part 2: Adding relationships](https://surrealdb.com/learn/fundamentals/relationships) [Introduction](https://surrealdb.com/learn/fundamentals/relationships) [Graph relations](https://surrealdb.com/learn/fundamentals/relationships/graph-relations) [Record links](https://surrealdb.com/learn/fundamentals/relationships/record-links) [Relational style joins](https://surrealdb.com/learn/fundamentals/relationships/relational-style) [Part 3: Making it schemafull](https://surrealdb.com/learn/fundamentals/schemafull) [Introduction](https://surrealdb.com/learn/fundamentals/schemafull) [Define tables, views and changefeeds](https://surrealdb.com/learn/fundamentals/schemafull/define-table) [Define fields, constraints and assertions](https://surrealdb.com/learn/fundamentals/schemafull/define-fields) [Schemafull CRUD](https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud) [Part 4: Making it secure](https://surrealdb.com/learn/fundamentals/security) [Introduction](https://surrealdb.com/learn/fundamentals/security) [Authentication](https://surrealdb.com/learn/fundamentals/security/authentication) [Query capabilities](https://surrealdb.com/learn/fundamentals/security/query-capabilities) [Part 5: Making it performant](https://surrealdb.com/learn/fundamentals/performance) [Introduction](https://surrealdb.com/learn/fundamentals/performance) [Indexing & data model considerations](https://surrealdb.com/learn/fundamentals/performance/index-data-model) [Deployment & storage layer considerations](https://surrealdb.com/learn/fundamentals/performance/deployment-storage) [Completion](https://surrealdb.com/learn/fundamentals/completion) Certification Pending completion # Updating data Now that we've returned from selecting things, it's time to update our knowledge of the `UPDATE` statement. We'll cover: - How to update one record, a range of records, or the entire table - The five different methods for updating data Let's start where we left off in our previous lesson on inserting data. ## Update with `INSERT` We briefly touched on how both the `INSERT` and `UPSERT` statements both `INSERT` and `UPDATE`. Let's expand a bit on the example of how to `UPDATE` with the `INSERT` statement before moving on to the `UPDATE` statement. ``` INSERT INTO product (id)VALUES (1),(1) ON DUPLICATE KEY UPDATE colours += ['Purple'];INSERT INTO product (id, colours) VALUES (2, ['Pink']), (2, ['Dark Heather Grey','Bubble Gum Pink', 'Purple'])ON DUPLICATE KEY UPDATE colours = $input.colours; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query We saw the first example in the previous lesson, but there is also another way to `UPDATE` data using the `$input` parameter. Here the `$input` parameter is an object that gives us access to all the fields of the record we are attempting to insert. We can therefore use the dot notation to select the sizes field that we are inserting and use that to update the sizes field that already exists. While this is possible if you need it, the `UPDATE` statement generally has a much better developer experience for updating, as we'll see. ## The five different methods for updating data ``` -- Update the currency field in the entire product tableUPDATE product SET currency = "USD";UPDATE product MERGE {currency: "USD"};UPDATE product PATCH [{ op: "replace", path: "currency", value: "USD"}];-- Update the entire product table to contain only the currency fieldUPDATE product CONTENT {currency: "USD"};-- Replace is an alias for CONTENTUPDATE product REPLACE {currency: "USD"}; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query Here's what the five different types of `UPDATE` statements did. We have `SET`, `MERGE` and `PATCH`, which can update individual fields in a record. Here we are updating the currency field in the entire product table. - `SET` uses familiar SQL syntax - `MERGE` does the same thing just using a JSON-like syntax - `PATCH` also does the same thing, just using the [JSON Patch specification](https://jsonpatch.com/) The [JSON Patch specification](https://jsonpatch.com/) is a proposed standard by the Internet Engineering Task Force (IETF). The purpose is to avoid sending a whole document when only a part has changed, used in combination with the `HTTP PATCH` method. This allows for partial updates using HTTP APIs in a standards-compliant way. Therefore we have: - `MERGE` which sends partial documents in our own simplified way - `PATCH` which sends partial documents in a standards-compliant way - It is more flexible than `MERGE` but with a somewhat more complex syntax Moving on to `CONTENT` and `REPLACE`. For the most part, `REPLACE` is just an alias for `CONTENT`. However, one difference is that you will see an error if you try to use `REPLACE` if it includes a field that is defined as read only. But if you use `CONTENT` that includes a value field that is read only, the query will still work and that part of the input will be ignored. So you can think of `REPLACE` as a stricter or slightly more aggressive form of `CONTENT`. What they have in common, however, is that they always send the whole document. Which means it effectively replaces the `CONTENT` that was there previously. ``` -- Update a single recordUPDATE product:01GRTTE7DG94R864R67MGDT0QM SET colours -= "Pink", colours += "Bubble Gum Pink", time.updated_at = time::now();UPDATE product:01GRTTE7DG94R864R67MGDT0QM PATCH [ { op: "remove", path: "colours/1" }, { op: "add", path: "colours", value: "Bubble Gum Pink" }, { op: "replace", path: "time.updated_at", value: time::now() }]; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query Let's explore another, more realistic example. Here we are updating a single record, which again, is the hoodie that we previously mentioned. We used to have this hoodie in the pink colour on our [SurrealDB.store](https://surrealdb.store/), but the manufacturer stopped using that colour, which meant we needed another pink colour, the Bubble Gum Pink. Therefore, we need to update the product table in our fictional Surreal Deal Store to reflect that. As we're replacing just an item in an array, we cannot use `MERGE` as it only works on entire fields, including nested fields inside objects like `time.updated_at` as long as you put the field path in quotes `“time.updated_at": time::now()`. We are then left with `SET` and `PATCH`, where we remove the pink colour from the array, add Bubble Gum Pink and finally update the `updated_at` time. We could simplify our `PATCH` to only use two replace operations, but I separated it into `remove` and `add` just for educational purposes here. ``` -- Update a range of records with record IDs (recommended if possible)UPDATE product:01FZ0CR6N09V5RG9RQ9A3264GX..=01G0MW4VTG8QZR3A4BTEXHXWS7SET currency = "USD", time.updated_at = time::now();-- Update a range of records with the where clauseUPDATE productSET currency = "USD", time.updated_at = time::now()WHERE time.created_at >= d"2022-10-19T00:01:53Z"AND time.created_at <= d"2022-10-26T18:00:05Z"; ``` ![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query You can also `UPDATE` a range of records, either by using record ranges or the where clause. Whenever you can, always use specific record IDs as that is the most efficient way. The `UPDATE` statement is also used to `DELETE` fields, but we'll cover that in the next lesson on deleting data. ## Summary Now that we've updated our knowledge of the `UPDATE` statement, let's summarise what we've learned. - The `UPDATE` statement - Can update one record, a range of records, or the entire table. - Has five different methods for updating data: - Using `SET` for a SQL-like experience - Using `MERGE` to merge-update only specific fields within a record like `SET` - Using `CONTENT` or its near-alias `REPLACE` to completely replace the record data - Using `PATCH` to use the JSON patch format for partial updates. Allowing for partial updates for HTTP APIs in a standards-compliant way. - Finally, it can also `DELETE` fields (covered in the next lesson on deleting data). Previous Reading data [Previous](https://surrealdb.com/learn/fundamentals/schemaless/reading-data) Next lesson Deleting data [Next lesson](https://surrealdb.com/learn/fundamentals/schemaless/deleting-data) ```json {"@context":"https://schema.org","@type":"Course","name":"SurrealDB Fundamentals","description":"The most efficient way to learn SurrealDB through guided hands-on learning","url":"https://surrealdb.com/learn/fundamentals","inLanguage":"en","isAccessibleForFree":false,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Welcome to SurrealDB University","url":"https://surrealdb.com/learn/fundamentals/intro/welcome"},{"@type":"LearningResource","name":"Intro to SurrealDB","url":"https://surrealdb.com/learn/fundamentals/intro/surrealdb"},{"@type":"LearningResource","name":"Why SurrealQL is SQL-like","url":"https://surrealdb.com/learn/fundamentals/intro/surrealql"},{"@type":"LearningResource","name":"Part 1: Schemaless CRUD","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Record IDs","url":"https://surrealdb.com/learn/fundamentals/schemaless/record-ids"},{"@type":"LearningResource","name":"Inserting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/inserting-data"},{"@type":"LearningResource","name":"Reading data","url":"https://surrealdb.com/learn/fundamentals/schemaless/reading-data"},{"@type":"LearningResource","name":"Updating data","url":"https://surrealdb.com/learn/fundamentals/schemaless/updating-data"},{"@type":"LearningResource","name":"Deleting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/deleting-data"},{"@type":"LearningResource","name":"Part 2: Adding relationships","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Graph relations","url":"https://surrealdb.com/learn/fundamentals/relationships/graph-relations"},{"@type":"LearningResource","name":"Record links","url":"https://surrealdb.com/learn/fundamentals/relationships/record-links"},{"@type":"LearningResource","name":"Relational style joins","url":"https://surrealdb.com/learn/fundamentals/relationships/relational-style"},{"@type":"LearningResource","name":"Part 3: Making it schemafull","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Define tables, views and changefeeds","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-table"},{"@type":"LearningResource","name":"Define fields, constraints and assertions","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-fields"},{"@type":"LearningResource","name":"Schemafull CRUD","url":"https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud"},{"@type":"LearningResource","name":"Part 4: Making it secure","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Authentication","url":"https://surrealdb.com/learn/fundamentals/security/authentication"},{"@type":"LearningResource","name":"Query capabilities","url":"https://surrealdb.com/learn/fundamentals/security/query-capabilities"},{"@type":"LearningResource","name":"Part 5: Making it performant","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Indexing \u0026 data model considerations","url":"https://surrealdb.com/learn/fundamentals/performance/index-data-model"},{"@type":"LearningResource","name":"Deployment \u0026 storage layer considerations","url":"https://surrealdb.com/learn/fundamentals/performance/deployment-storage"},{"@type":"LearningResource","name":"Completion","url":"https://surrealdb.com/learn/fundamentals/completion"}]} ``` ```json {"@context":"https://schema.org","@type":"LearningResource","name":"Updating data","description":"Updating data","url":"https://surrealdb.com/learn/fundamentals/schemaless/updating-data","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},"position":11} ``` ```json {"@context":"https://schema.org","@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the unified data layer for AI. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]} ``` ```json {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Updating data","item":"https://surrealdb.com/learn/fundamentals/schemaless/updating-data"}]} ```