# UPDATE Statement Syntax

An UPDATE statement is used to modify the values of existing rows in a table.

UPDATE statements are helpful for correcting bad data, backfilling missing values, or updating status fields without deleting and reinserting rows.

Note
UPDATE statement support in Trino is still experimental.

## Syntax

```sql
UPDATE <table_name> SET <column_name> = <value>[, <column_name> = <value> ...] WHERE <condition>
```

Where:

- <table_name> is the name of the table to update
- <column_name> = <value> sets the new value for a column
- <condition> is the clause to specify a search condition


`UPDATE` statement without `WHERE` clause isn't allowed to reduce the occurrence of unintentional data modification.

### Examples

Update the status of all line items shipped by air:

```sql
UPDATE lineitem SET status = 'SHIPPED' WHERE shipmode = 'AIR'
```

Mark event logs from July 1st, 2017 UTC as processed:

```sql
UPDATE lineitem SET processed = true WHERE TD_TIME_RANGE(time, '2017-07-01','2017-07-02')
```

## Limitations

### No UPDATE Without a WHERE Clause

To reduce the occurrence of unintentional data modification, UPDATE statements require you to include a WHERE clause.

Error sample:

```
Does not support UPDATE statement without WHERE condition
```

### UPDATE against Data Tank

UPDATE statements cannot be issued against data stored in Data Tank.
Connect directly to the PostgreSQL Data Tank to issue UPDATE statements on data stored there.

### Limit number of partitions to update at once

A single UPDATE query can update up to 500k partitions.
An UPDATE query fails when it exceeds the limit.

#### Reduce affected rows of UPDATE statement

When an UPDATE query fails due to the limit exceeded error or timeout error, you should rewrite the UPDATE statement to reduce affected rows. The most effective way to do this is to include a WHERE clause that limits the time range for the UPDATE with `TD_TIME_RANGE` or `TD_INTERVAL`. For example, if you have a year's worth of game history data, instead of

```
UPDATE game_history SET active = false WHERE player_id=1000;
```

try

```
UPDATE game_history SET active = false
  WHERE player_id=1000
  AND TD_TIME_RANGE(time, '2018-01-01', '2018-02-01','PDT')
```

and then update more time ranges until all rows are updated.

### UPDATE statement Timeout Error

An UPDATE query can take several hours when affected rows of the UPDATE statement are very large. The query may time out and the job fail in this case. If the job fails by a timeout error, you should rewrite the UPDATE statement to affect fewer rows. Refer to [Reduce affected rows of UPDATE statement](#reduce-affected-rows-of-update-statement).

### Concurrent DELETE/UPDATE statements on the same table may conflict

When multiple UPDATE statements are executed concurrently on the same table, the execution time may be longer or the query may fail. It is because of the conflict of underlying partition file modification. The general guideline is to avoid concurrent UPDATE statement execution since the possibility of conflict is unpredictable. The possibility depends on various factors, like the condition of the UPDATE statement, the data volume and the data distribution of the table.

When an UPDATE statement fails due to conflict, please retry the query.

### UPDATE Statement Resource Consumption

UPDATE queries issue jobs and consume resources. Because of the storage and indexing methods used in Treasure AI, an UPDATE query can be resource intensive.

To prevent an UPDATE query from performing a full table scan, you should use:

- a time expression, like TD_TIME_RANGE or TD_INTERVAL, if the table uses default time-based partitioning
- an equality predicate on all partition keys, if the table uses user-defined partitioning


For example, if your table is partitioned by time, and you are trying to update a set of users that were created in June 2018, include the time restriction even if that's not a critical parameter for your UPDATE statement.

For example, you should:

```
UPDATE logtable SET status = 'reviewed' WHERE userid IN (1234, 1235, 1236) AND TD_TIME_RANGE(time, '2018-06-01','2018-07-01')
```

Including the time restriction significantly improves the speed of your query, and reduces the resources this query consumes.

## Undo Update Statements

If you want to rollback an executed update statement and recover the previous values, you must contact Treasure AI Support and provide the jobID of the job that contained the executed UPDATE statement. In many cases, you can undo an update statement. However, certain situations prevent the rollback of UPDATE statements.

Situations that prevent rollback include:

- When new partitions have been modified by another UPDATE statement.
- When original partitions have been discarded, which can happen after a retention period.
- When a table is dropped and a new table with the same name is created.
- When new partitions have been further modified or replaced, which can happen as a result of internal storage maintenance processes.