> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Update Data

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

This page has instructions for updating existing rows of data in CockroachDB, using the following <InternalLink path="sql-statements">SQL statements</InternalLink>:

* [`UPDATE`](#use-update), which updates existing rows in a table.
* [`UPSERT`](#use-upsert), which inserts new rows in a table, updating existing rows that conflict on a primary key.
* [`INSERT... ON CONFLICT... DO UPDATE`](#use-insert-on-conflict), which inserts new rows in a table, updating existing rows in the event of a conflict on specified, `UNIQUE` -constrained columns.

## Before you begin

Before reading this page, do the following:

* <InternalLink version="cockroachcloud" path="quickstart">Create a CockroachDB Standard cluster</InternalLink> or <InternalLink version="cockroachcloud" path="quickstart?filters=local">start a local cluster</InternalLink>.
* <InternalLink path="install-client-drivers">Install a Driver or ORM Framework</InternalLink>.
* <InternalLink path="connect-to-the-database">Connect to the database</InternalLink>.
* <InternalLink path="schema-design-overview">Create a database schema</InternalLink>.
* <InternalLink path="insert-data">Insert data</InternalLink> that you now want to update.

  In the examples on this page, we use sample <InternalLink path="movr">`movr`</InternalLink> data imported with the <InternalLink path="cockroach-workload">`cockroach workload` command</InternalLink>.

## Use `UPDATE`

To update existing rows in a table, use an <InternalLink path="update">`UPDATE` statement</InternalLink> with a `WHERE` clause that filters on the columns that identify the rows that you want to update.

<Note>
  To update a large number of rows (i.e., tens of thousands of rows or more), we recommend iteratively updating subsets of the rows that you want to update, until all of the rows have been updated. You can write a script to do this, or you can write a loop into your application.

  For guidance and an example, see <InternalLink path="bulk-update-data">Bulk-update Data</InternalLink>.
</Note>

### `UPDATE` SQL syntax

In SQL, `UPDATE` statements generally take the following form:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE {table} SET {update_column} = {update_value} WHERE {filter_column} = {filter_value}
```

Where:

* <code>{'{table}'}</code> is the table to update.
* <code>{'{update_column}'}</code> is the column to update.
* <code>{'{update_value}'}</code> is the new value of the column in the matching row.
* <code>{'{filter_column}'}</code> is the column to filter on.
* <code>{'{filter_value}'}</code> is the matching value for the filter.

<Tip>
  For detailed reference documentation on the `UPDATE` statement, including additional examples, see the <InternalLink path="update">`UPDATE` syntax page</InternalLink>.
</Tip>

### `UPDATE` best practices

Here are some best practices to follow when updating rows:

* Always specify a `WHERE` clause in `UPDATE` queries. If no `WHERE` clause is specified, CockroachDB will update all of the rows in the specified table.
* To update a large number of rows (i.e., tens of thousands of rows or more), use a <InternalLink path="bulk-update-data">batch-update loop</InternalLink>.
* When executing `UPDATE` statements from an application, make sure that you wrap the SQL-executing functions in <InternalLink path="query-behavior-troubleshooting#transaction-retry-errors">a retry loop that handles transaction errors</InternalLink> that can occur under <InternalLink path="performance-best-practices-overview#transaction-contention">contention</InternalLink>.

### `UPDATE` example

Suppose you want to change the status of all of the vehicles owned by a particular MovR user. To update all rows in the `vehicles` table with the `owner_id` equal to `bd70a3d7-0a3d-4000-8000-000000000025`:

```sql SQL theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE vehicle SET status = 'unavailable' WHERE owner_id = 'bd70a3d7-0a3d-4000-8000-000000000025';
```

```go Go (lib/pq) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// 'conn' is an open database connection

ownerID := "bd70a3d7-0a3d-4000-8000-000000000025"

err = conn.QueryRow(context.Background(), "UPDATE vehicle SET status = 'unavailable' WHERE owner_id = $1", ownerID)
    if err != nil {
        fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
        os.Exit(1)
    }
```

```java Java (JDBC) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// ds is an org.postgresql.ds.PGSimpleDataSource

String ownerId = "bd70a3d7-0a3d-4000-8000-000000000025";

try (Connection connection = ds.getConnection()) {
    PreparedStatement p = connection.prepareStatement("UPDATE vehicles SET status = 'unavailable' WHERE owner_id = ?");
    p.setString(1, ownerId);
    p.executeUpdate();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
            e.getMessage());
}
```

```python Python (psycopg2) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
# conn is a psycopg2 connection

ownerID = 'bd70a3d7-0a3d-4000-8000-000000000025'

with conn.cursor() as cur:
    cur.execute(
        "UPDATE vehicles SET status = 'unavailable' WHERE owner_id = %s", (ownerID,))
```

## Use `UPSERT`

To insert new rows into a table, and update rows that conflict with the primary key value(s) of the new rows, use an <InternalLink path="upsert">`UPSERT` statement</InternalLink>.

### `UPSERT` SQL syntax

In SQL, `UPSERT` statements take the following form:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPSERT INTO {table} ({upsert_columns}) VALUES ({upsert_values});
```

Where:

* <code>{'{table}'}</code> is the table to update.
* <code>{'{upsert_columns}'}</code> is a comma-separated list of the columns to which you want to insert values.
* <code>{'{upsert_values}'}</code> is a comma-separated list of values that you want to insert.

<Tip>
  For detailed reference documentation on the `UPSERT` statement, including additional examples, see the <InternalLink path="upsert">`UPSERT` syntax page</InternalLink>.
</Tip>

### `UPSERT` best practices

Here are some best practices to follow when using `UPSERT`:

* Limit the number of `UPSERT` statements that you execute. It's more efficient to insert multiple rows with a single statement than to execute multiple `UPSERT` statements that each insert a single row.
* When executing `UPSERT` statements from an application, make sure that you wrap the SQL-executing functions in <InternalLink path="query-behavior-troubleshooting#transaction-retry-errors">a retry loop that handles transaction errors</InternalLink> that can occur under contention.

### `UPSERT` example

Suppose you want to add some promo codes to the MovR platform, and overwrite any existing promos with the same code. To insert new rows into the `promo_codes` table, and update any rows that have the same primary key `code` value:

```sql SQL theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPSERT INTO promo_codes (code, description, rules)
  VALUES ('0_explain_theory_something','Fifteen percent off.', '{"type": "percent_discount", "value": "15%"}'),
    ('100_address_garden_certain','Twenty percent off.','{"type": "percent_discount", "value": "20%"}'),
    ('1000_do_write_words','Twenty-five percent off.','{"type": "percent_discount", "value": "25%"}');
```

```go Go (lib/pq) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// 'db' is an open database connection

codeOne := "0_explain_theory_something"
descriptionOne := "Fifteen percent off."
rulesOne := `{"type": "percent_discount", "value\": "15%"}`
codeTwo := "100_address_garden_certain"
descriptionTwo := "Twenty percent off."
rulesTwo := `{"type": "percent_discount", "value": "20%"}`
codeThree := "1000_do_write_words"
descriptionThree := "Twenty-five percent off."
rulesThree := `{"type": "percent_discount", "value": "25%"}`

if _, err := db.Exec("UPSERT INTO promo_codes (code, description, rules) "+
  "values ($1, $2, $3), ($4, $5, $6), ($7, $8, $9)",
  codeOne, descriptionOne, rulesOne, codeTwo, descriptionTwo,
  rulesTwo, codeThree, descriptionThree, rulesThree); err != nil {
  return err
}
return nil
```

```java Java (JDBC) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// ds is an org.postgresql.ds.PGSimpleDataSource

String codeOne = "0_explain_theory_something";
String descriptionOne = "Fifteen percent off.";
String rulesOne = "{\"type\": \"percent_discount\", \"value\": \"15%\"}";
String codeTwo = "100_address_garden_certain";
String descriptionTwo = "Twenty percent off.";
String rulesTwo = "{\"type\": \"percent_discount\", \"value\": \"20%\"}";
String codeThree = "1000_do_write_words";
String descriptionThree = "Twenty-five percent off.";
String rulesThree = "{\"type\": \"percent_discount\", \"value\": \"25%\"}";

try (Connection connection = ds.getConnection()) {
    PreparedStatement p = connection.prepareStatement("UPSERT INTO promo_codes (code, description, rules) values (?, ?, ?), (?, ?, ?), (?, ?, ?)");
    p.setString(1, codeOne);
    p.setString(2, descriptionOne);
    p.setString(3, rulesOne);
    p.setString(4, codeTwo);
    p.setString(5, descriptionTwo);
    p.setString(6, rulesTwo);
    p.setString(7, codeThree);
    p.setString(8, descriptionThree);
    p.setString(9, rulesThree);
    p.executeUpdate();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
            e.getMessage());
}
```

```python Python (psycopg2) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
# conn is a psycopg2 connection

codeOne = '0_explain_theory_something'
descriptionOne = 'Fifteen percent off.'
rulesOne = '{"type": "percent_discount", "value": "15%"}'
codeTwo = '100_address_garden_certain'
descriptionTwo = 'Twenty percent off.'
rulesTwo = '{"type": "percent_discount", "value": "20%"}'
codeThree = '1000_do_write_words'
descriptionThree = 'Twenty-five percent off.'
rulesThree = '{"type": "percent_discount", "value": "25%"}'

with conn.cursor() as cur:
    cur.execute("UPSERT INTO promo_codes (code, description, rules) values (%s,%s,%s), (%s,%s,%s), (%s,%s,%s)",
                (codeOne, descriptionOne, rulesOne, codeTwo, descriptionTwo, rulesTwo, codeThree, descriptionThree, rulesThree))
```

## Use `INSERT ON CONFLICT`

To insert new rows into a table, and to update rows with `UNIQUE`-constrained values that conflict with any values of the new rows, use an `INSERT... ON CONFLICT... DO UPDATE` statement.

`INSERT... ON CONFLICT... DO UPDATE` is semantically identical to `UPSERT`, when the conflicting values are in the primary key and the action to take on conflict is to update the conflicting rows with the new rows. `INSERT... ON CONFLICT` is more flexible than `UPSERT`, and can be used to consider uniqueness for columns not in the primary key. With `INSERT... ON CONFLICT`, you can also control how to update rows in the event of a conflict. This contrasts with the behavior of an `UPSERT` statement, which just overwrites conflicting rows with new rows.

<InternalLink path="hash-sharded-indexes">Hash-sharded indexes</InternalLink> can be used for uniqueness checks.

<Note>
  Note that if you are inserting to/updating all columns of a table, and the table has no secondary indexes, `UPSERT` will be faster than the equivalent `INSERT ON CONFLICT` statement, as it will write without first reading.
</Note>

### `INSERT ON CONFLICT` SQL syntax

In SQL, `INSERT... ON CONFLICT... DO UPDATE` statements take the following form:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO {table} ({insert_columns}) VALUES ({insert_values})
  ON CONFLICT ({conflict_columns}) DO UPDATE SET {update_column} = {update value};
```

Where:

* <code>{'{table}'}</code> is the table to update.
* <code>{'{insert_columns}'}</code> is a comma-separated list of the columns to which you want to insert values.
* <code>{'{insert_values}'}</code> is a comma-separated list of values that you want to insert.
* <code>{'{conflict_columns}'}</code> is a comma-separated list of the columns to evaluate for a conflict.
* <code>{'{update_column}'}</code> is the column to update, on conflict.
* <code>{'{update_values}'}</code> is the value to assign the update column.

Note that the statement contains an `UPDATE` clause, which is semantically identical to an `UPDATE` statement.

### `INSERT ON CONFLICT` best practices

Here are some best practices to follow when using `INSERT... ON CONFLICT... DO UPDATE`:

* Limit the number of `INSERT` statements that you execute. It's more efficient to insert multiple rows with a single statement than to execute multiple `INSERT` statements that each insert a single row.
* When executing `INSERT` statements from an application, make sure that you wrap the SQL-executing functions in <InternalLink path="query-behavior-troubleshooting#transaction-retry-errors">a retry loop that handles transaction errors</InternalLink> that can occur under contention.
* Follow the <InternalLink path="insert#performance-best-practices">performance best practices listed on the `INSERT`</InternalLink> page.

### `INSERT ON CONFLICT` example

Suppose you want to record a particular user's promo code usage count. The `user_promo_codes` table keeps track of user promo usage. If no usage counter exists, you want to insert a new row, and if one does exist, you want to increment the `usage_count` column by 1.

```sql SQL theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 1)
    ON CONFLICT (city, user_id, code)
    DO UPDATE SET usage_count = user_promo_codes.usage_count;
```

```go Go (lib/pq) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// 'db' is an open database connection

city := "new york"
userId := "147ae147-ae14-4b00-8000-000000000004"
code := "promo_code"
ts := "now()"
usageCount := 1

if _, err := db.Exec("INSERT INTO user_promo_codes "+
  "VALUES ($1, $2, $3, $4, $5) ON CONFLICT (city, user_id, code) "+
  "DO UPDATE SET usage_count = user_promo_codes.usage_count + 1",
  city, userId, code, ts, usageCount); err != nil {
  return err
}
return nil
```

```java Java (JDBC) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// ds is an org.postgresql.ds.PGSimpleDataSource

String city = "new york";
String userId = "147ae147-ae14-4b00-8000-000000000004";
String code = "promo_code";
String ts = "now()";
int usageCount = 1;

try (Connection connection = ds.getConnection()) {
    PreparedStatement p = connection.prepareStatement("INSERT INTO user_promo_codes VALUES (?, ?, ?, ?, ?) ON CONFLICT (city, user_id, code) DO UPDATE SET usage_count = user_promo_codes.usage_count+1");
    p.setString(1, city);
    p.setString(2, userId);
    p.setString(3, code);
    p.setString(4, ts);
    p.setInt(5, usageCount);
    p.executeUpdate();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
            e.getMessage());
}
```

```python Python (psycopg2) theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
# conn is a psycopg2 connection

city = 'new york'
userId = '147ae147-ae14-4b00-8000-000000000004'
code = 'promo_code'
ts = 'now()'
usageCount = 1

with conn.cursor() as cur:
    cur.execute("INSERT INTO user_promo_codes VALUES (%s, %s, %s, %s, %s) ON CONFLICT (city, user_id, code)"
                "DO UPDATE SET usage_count = user_promo_codes.usage_count+1", (city, userId, code, ts, usageCount))
```

## See also

Reference information related to this task:

* <InternalLink path="update">`UPDATE`</InternalLink>
* <InternalLink path="bulk-update-data">Bulk-update Data</InternalLink>
* <InternalLink path="update">`UPSERT`</InternalLink>
* <InternalLink path="insert#on-conflict-clause">`INSERT... ON CONFLICT`</InternalLink>
* <InternalLink path="performance-best-practices-overview#transaction-contention">Transaction Contention</InternalLink>

Other common tasks:

* <InternalLink path="connect-to-the-database">Connect to the Database</InternalLink>
* <InternalLink path="insert-data">Insert Data</InternalLink>
* <InternalLink path="query-data">Query Data</InternalLink>
* <InternalLink path="delete-data">Delete Data</InternalLink>
* <InternalLink path="bulk-update-data">Bulk-update Data</InternalLink>
* <InternalLink path="run-multi-statement-transactions">Run Multi-Statement Transactions</InternalLink>
* <InternalLink path="query-behavior-troubleshooting">Error Handling and Troubleshooting</InternalLink>
* <InternalLink path="make-queries-fast">Optimize Statement Performance</InternalLink>
* <InternalLink path="example-apps">Example Apps</InternalLink>