Insert and update data

Data can be inserted or updated in tables.

INSERT and UPDATE operations in CQL are the same operation. If the specified primary key already exists, the existing row is updated with the new data. If the specified primary key does not exist, a new row is created.

Insert, update, and delete operations on rows sharing the same partition key for a table are performed atomically and in isolation.

To insert or update data, you must have a keyspace and a table with defined columns.

Use INSERT and UPDATE commands

Use the INSERT and UPDATE commands to write new data to table or replace existing data.

For example, to insert a simple record into a table named cycling.cyclist_name:

INSERT INTO cycling.cyclist_name 
    (id, lastname, firstname) 
     VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');

Insert complex string constants using double dollar signs ($$) to enclose a string with quotes, backslashes, or other characters that would normally need to be escaped:

INSERT INTO cycling.calendar 
  (race_id, race_start_date, race_end_date, race_name) 
   VALUES
  (201, '2015-02-18', '2015-02-22', $$Women's Tour of New Zealand$$);

For information about JSON data, see Insert JSON formatted values.

Bulk inserts

You can use the COPY TO and COPY FROM commands to export and import data in CSV files.

You can use BATCH to group multiple operations into one statement.

Inserts by data type

The way you update data and the outcome of an INSERT or UPDATE statement depends on the column’s data type and definition.

The following sections describe how to perform INSERT and UPDATE operations for specific data types.

Collections (maps, lists, sets)

See Collections in CQL.

Counters

Counter columns can only be updated, not inserted.

The following example updates a counter column named popularity by incrementing the counter by 2:

UPDATE cycling.popular_count SET popularity = popularity + 2 
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

For more information about using and updating counter columns, see Create counter columns.

Dates and times

The following sections describe how to insert and update data for date and time data types.

Date

Write values into a date column using the current date or a string formatted date.

Function
String format

Use toDate(now()) to insert the current date into a date field:

INSERT INTO cycling.cyclist_alt_stats (id,last_race) 
  VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,todate(now()));
INSERT INTO cycling.cyclist_alt_stats (id,last_race) 
  VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,todate(now()));

You can also use this function to update a date field:

UPDATE cycling.cyclist_alt_stats SET last_race = toDate(now())
  WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;

Use the date string format:

<yyyy>-<mm>-<dd>

The elements of the date are separated with a hyphen:

yyyy: four-digit year
mm: two-digit month
dd: two-digit day

For example, May 5, 2017 would be 2017-05-05.

Insert a specific date string:

INSERT INTO cycling.cyclist_alt_stats (id,first_race) 
  VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,'2006-03-15');
INSERT INTO cycling.cyclist_alt_stats (id,first_race) 
  VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,'2006-03-15');

Update a date field with a specific date string:

UPDATE cycling.cyclist_alt_stats SET birthday = '1987-03-07'
  WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;

After inserting or updating a date field, you can use a SELECT statement to retrieve the value:

SELECT first_race, last_race, birthday FROM cycling.cyclist_alt_stats
  WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;

Results

 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2024-06-21 | 1989-07-05

(1 rows)

Duration

Use a string formatted duration value to upsert a duration column. There are multiple possible duration formats. For details, see the duration format reference.

Don’t use quotes for duration values.

Insert a duration value:

INSERT INTO cycling.race_times (
  finish_time, race_name, cyclist_name, race_date
) VALUES (
  1h4m48s20ms,
  '17th Santos Tour Down Under', 'Rohan DENNIS', '2017-04-14'
);

Update a duration value:

UPDATE cycling.race_times SET finish_time = 2h5m22s14ms
WHERE race_name = '17th Santos Tour Down Under'
  AND cyclist_name = 'Rohan DENNIS'
  AND race_date = '2017-04-14';

Time

Write values into a time column using the time string format:

time string format:

<HH>:<MM>:<SS>[.<fffffffff>]

Where the following values are expected:

HH: two-digit hour using a 24 hour clock.
MM: two-digit minute.
SS: two-digit seconds.
(Optional) fffffffff: up to nine-digit nanoseconds, when excluded it’s set to zero (0).

For example, 2:30 PM can be represented in any of the following formats:

14:30:00
14:30:00.000
14:30:00.000000000

For example:

Insert data into a time column:

INSERT INTO cycling.cyclist_races (
  id,
  races
) VALUES (
   5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
   [ { race_time : '07:00:00'},
     { race_time : '08:00:00' } ]
);

Update a time field:

UPDATE cycling.cyclist_races SET races[1] = { race_time : '06:00:00'}
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 ;

Timestamp

Upsert the current date/time or a specific date/time into a timestamp field using the timestamp string format or functions.

Functions
String format

Use functions to set the current timestamp:

Current date and time into timestamp field: toTimestamp(now()) sets the timestamp to the current time of the coordinator.
Current date at midnight into timestamp field: toTimestamp(toDate(now())) sets the timestamp to the current date with the time set to midnight.

Date with time set to midnight using UTC:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0,toTimeStamp(toDate(now())));

Full timestamp:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0,toTimeStamp(now()));

When writing a specific timestamp, use the timestamp string format.

The timestamp format combines both date and time with the addition of timezone in ISO 8601 format:

yyyy-mm-dd[(T| )HH:MM:SS[.fff]][(+|-)<NNNN>]

For more information about the contents and variations of the timestamp string, see the timestamp format reference.

Only the date portion is required. If time and timezone aren’t specified, the time is set to midnight and the timezone is set to UTC. The following example would set the timestamp to 2017-04-01 00:00:00.000000+0000.

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01');

If the date and time are specified but the timezone is not specified, the timezone is set to UTC. The following example sets the time to midnight, but it doesn’t set the timezone. The resulting timestamp is 2017-04-01 00:00:00.000000+0000.

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01+0000');

If all parts are specified, the given timestamp is used. This example provides a full timestamp with timezone set to UTC:

INSERT INTO cycling.comments (id, created_at) 
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01T11:21:59.001+0000');

After writing a timestamp, use a SELECT statement to get the timestamp:

SELECT created_at FROM cycling.comments 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
 LIMIT 1;

Results

 created_at
---------------------------------
 2024-06-21 00:00:00.000000+0000

(1 rows)

Static

Static columns are updated in a similar way to other columns: Specify the new value in your INSERT or UPDATE statement. The new value is applied to all rows in the partition.

These examples show a static column named flag being set to various values (1, 2, 3):

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'Belgium', 'Jacques', 1
);

INSERT INTO cycling.country_flag (
  country, cyclist_name
) VALUES (
  'Belgium', 'Andre'
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'Andre', 2
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'George', 3
);

Tuples

Tuples group small amounts of data together and store the data in a single column.

Tuples are frozen by default, so you cannot update individual fields in a tuple. Instead, you must provide values for all fields of the tuple.

For example:

Define a table with a tuple column.

This example creates a table named nation_rank in the cycling keyspace with a tuple column named info. The tuple stores an integer, text, and integer values.
```
CREATE TABLE IF NOT EXISTS cycling.nation_rank (
  nation text PRIMARY KEY,
  info tuple<int, text, int>
);
```

Insert rows into the table, enclosing tuple values in parentheses:

INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'Spain', (1, 'Alejandro VALVERDE', 9054)
);

INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'France', (2, 'Sylvain CHAVANEL', 6339)
);

INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'Belgium', (3, 'Phillippe GILBERT', 6222)
);

INSERT INTO cycling.nation_rank (
  nation, info
) VALUES (
  'Italy', (4, 'Davide REBELLINI', 6090)
);

Tuples can contain nested tuples. For example:

Define a table with a nested tuple column:

CREATE TABLE IF NOT EXISTS cycling.route (
  race_id int,
  race_name text,
  point_id int,
  lat_long tuple<text, tuple<float, float>>,
  PRIMARY KEY (race_id, point_id)
);

Insert tuple data into the table, enclosing both the inner and outer tuple values in parentheses:

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 1, ('Onnens', (46.8444, 6.6667))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 2, ('Champagne', (46.833, 6.65))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 3, ('Novalle', (46.833, 6.6))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 4, ('Vuiteboeuf', (46.8, 6.55))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 5, ('Baulmes', (46.7833, 6.5333))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 6, ('Les Clées', (46.7222, 6.5222))
);

UDTs

See Insert or update UDT data.

UUIDs

See UUID and timeuuid.