# Cratedb update a column with 3 million records

**URL:** <https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223>\
**Category:** CrateDB\
**Created:** [September 20, 2022, 5:45pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223 "2022-09-20T17:45:10Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [September 20, 2022, 5:45pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/1 "2022-09-20T17:45:10Z")

</div>

Hello community. so I am running an update query.

```auto
UPDATE "transactions_v2" set "itemPrices" = NULL;

```

My table has about 3 million records in it. When running this query I get an error.

```auto
Error!

[object Object]

```

I can’t see the details of the error either. Is there a better way to run an update query with millions of records.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 20, 2022, 5:54pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/2 "2022-09-20T17:54:28Z")

</div>

Could you maybe share the table information and CrateDB version.

Are you using the Admin UI, and if so did you enable the “Show error trace” option?

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [September 20, 2022, 6:20pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/3 "2022-09-20T18:20:20Z")

</div>

> [@proddata](#):
>
> Are you using the Admin UI, and if so did you enable the “Show error trace” option?

 ![Screen Shot 2022-09-20 at 2.19.23 PM](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/1a4669f0d4f7167b0f75c4d7308d6450a2699877.png)

Crate version is: Version:4.6.7.

I am not getting anything logged for the error trace.

Thank you!

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 20, 2022, 6:46pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/4 "2022-09-20T18:46:59Z")

</div>

This seems to be a bug.  
Could you try upgrading to a newer CrateDB version (i.e. 4.8.4 or 5.0.1) and see if this persists.

Otherwise since we are talking only about 3 million records copying the table without the column might be an option

```sql
SHOW CREATE TABLE transactions_v2;
CREATE TABLE transactions_v3 ...
INSERT INTO transactions_v3 (<columns,...>) SELECT <columns,...> FROM transactions_v2;
ALTER CLUSTER SWAP TABLE transactions_v3 TO transactions_v2

```

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [September 26, 2022, 2:38pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/5 "2022-09-26T14:38:28Z")

</div>

Gotchya, I am running into some issues when running the same update query.

```auto
Error!

SQLParseException[Couldn't create execution plan from logical plan because of: the shard 1 of table [scp_service_transaction.transactions_v2/YQFVfMZHRXinZwfALmruKQ] is not available: Fetch["itemPrices"] └ Limit[100::bigint;0] └ Collect[scp_service_transaction.transactions_v2 | [_fetchid] | true]]

```

I am unable to query my table and I get the error above. The health on my table is also labeled “red”. Any way I can fix my table so I can query again? I ran the update query multiple times.

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [September 26, 2022, 3:24pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/6 "2022-09-26T15:24:41Z")

</div>

> [@arafaraf](#):
>
> I am unable to query my table and I get the error above. The health on my table is also labeled “red”. Any way I can fix my table so I can query again? I ran the update query multiple times.

Fixed by running

```auto
ALTER CLUSTER REROUTE RETRY FAILED

```

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 26, 2022, 3:45pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/7 "2022-09-26T15:45:46Z")

</div>

did this fix the original problem?

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [September 26, 2022, 4:25pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/8 "2022-09-26T16:25:57Z")

</div>

No it did not fix the original problem where the update query was returning an error. After doing some more investigation I was running queries like

```auto
UPDATE "scp_service_transaction"."transactions_v2" set "itemPrices" = NULL WHERE "itemPrices" IS NOT NULL;

```

That seemed to return “OK” for me.

My question is how would I run an update query in batches? So I want to run an update query where we take the first 500 records and update, then take the next 500 and update all the way until reaching the total number of records in the database. It seems like thats the only way to update the whole table without the table reaching bad health.

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [September 27, 2022, 12:18pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/9 "2022-09-27T12:18:26Z")

</div>

Hi,  
In other systems it is important to batch operations like this and you would find implementations of the SQL UPDATE command that would take a LIMIT clause or equivalent or let you JOIN the table to be updated with a lookup table. These options are not currently available in CrateDB, you could always use your client process to look up the PK values of the records that need updating first and then run the UPDATE against 500 of those records at a time, but the reality is that this should not be needed in CrateDB, there is no locking, and atomicity is at the document level, so the command should work just fine for millions of records. Are you seeing any issues now that you managed to get pass the error message you had initially?  
Another option to consider is to copy the rest of the data into a new table and then swap the tables.

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [September 27, 2022, 2:07pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/10 "2022-09-27T14:07:18Z")

</div>

So when I run the update command against millions of records the best I was able to get was update 2.2 million records. What do you mean by PK values? How do I look up the PK values? For me copying data to another table is not really an option because in Production everything is pointing to that one table and used by many microservices. I am still seeing the same issue running the update query where I get.

```auto
Error!

[Object Object]

```

I have to run the update query about two or three times to fully update all the columns in the database.

The two update queries I am working with are

````auto
UPDATE "scp_service_transaction"."transactions_v2" set "itemPrices" = NULL WHERE "itemPrices" is Not Null;
``` and

````

UPDATE “scp\_service\_transaction”.“transactions\_v2”  
SET “itemPrices” = extract\_item\_prices(“serializedTransaction”);

```auto
I defined 'extract_item_prices' as a javscript function in cratedb.

For the second query I wrote a migration script in order to update the DB but the best result I got was updating 2.3 million records out of 2.9 million.
```

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [September 27, 2022, 2:18pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/11 "2022-09-27T14:18:11Z")

</div>

Apologies, I misunderstood that after the `REROUTE RETRY FAILED` you were able to successfully run to completion the `UPDATE with WHERE "itemPrices" IS NOT NULL`

Could you check if there are additional error details in /var/log/crate/crate.log ?

By PK I mean primary key values, if you did not define a primary key in this table there will still be an \_id column (see [System columns — CrateDB: Reference](https://crate.io/docs/crate/reference/en/5.0/general/ddl/system-columns.html) )  
you could for instance run

```sql
SELECT _id
FROM "scp_service_transaction"."transactions_v2"
WHERE "itemPrices" is Not Null
LIMIT 500

```

and then

```sql
UPDATE "scp_service_transaction"."transactions_v2"
SET "itemPrices" = NULL 
WHERE _id IN (...)

```

until the first query does not return any results anymore

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [September 28, 2022, 5:33pm UTC](https://community.cratedb.com/t/cratedb-update-a-column-with-3-million-records/1223/12 "2022-09-28T17:33:26Z")

</div>

So I came up with a solution

```auto
UPDATE "scp_service_transaction"."transactions_v2"
SET "itemPrices"= extract_item_prices("serializedTransaction")
WHERE "transactionId" in (
    SELECT "transactionId" from "scp_service_transaction"."transactions_v2" where "itemPrices" IS NULL AND get_array_length("itemPrices") = -1 limit 500
  );

```

I wrote a migration script with a while loop where once the update query returns a rowcount of zero we stop updating. Thanks guys!