Insert or update UDT data

Use an INSERT or UPDATE statement to insert or change data in a user-defined type (UDT) column.

This command requires a keyspace, table, and UDT.

Insert or update an entire UDT record

Set or replace an entire UDT using an INSERT or UPDATE statement.

Enclose the UDT with curly braces, separating each key-value pair with a colon:

INSERT INTO cycling.cyclist_stats 
  (id, lastname, basics) 
  VALUES (
    e7ae5cf3-d358-4d99-b900-85902fda9bb0,
    'FRAME',
    { birthday:'1993-06-18', nationality:'New Zealand', weight:null, height:null }
  );

Note the inclusion of null values for UDT elements that have no value. Your statement must include a value, null or otherwise, for each element of the UDT.

If the UDT is nested in another column type, such as collection, the UDT’s curly braces are enclosed within the collection’s syntax. For example, assume you have a list of races where the race name, date, and time are defined in a UDT. In this case, the UDT elements are enclosed in curly braces that are also enclosed in the list’s square brackets:

INSERT INTO cycling.cyclist_races (
  id,
  lastname,
  firstname,
  races
) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  'VOS',
  'Marianne',
  [ {
       race_title:'Rabobank 7-Dorpenomloop Aalburg',
       race_date:'2015-05-09',
       race_time:'02:58:33'},
     {
       race_title:'Ronde van Gelderland',
       race_date:'2015-04-19',
       race_time:'03:22:23'
     }
  ]
);

The UDT nested in the list is frozen, so the entire list is read when querying the table, and the entire list must be replaced when updating the list.

Update individual fields in a UDT

If a UDT isn’t frozen and doesn’t include collection fields, then you can update individual field values.

UDTs with collection fields must be frozen in the table definition. Therefore, you cannot update the individual fields in these UDTs. Instead, you must replace the entire UDT record.

For non-frozen, non-collection UDTs, use an UPDATE statement with the desired key-value pair definitions For example:

A UDT is defined in the table with basics basic_info:

CREATE TABLE IF NOT EXISTS cycling.cyclist_stats ( 
  id UUID PRIMARY KEY,
  lastname text,
  basics basic_info
);

A row is inserted with values for the UDT:

INSERT INTO cycling.cyclist_stats 
  (id, lastname, basics) 
  VALUES (
    220844bf-4860-49d6-9a4b-6b5d3a79cbfb,
    'TIRALONGO',
    { birthday:'1977-07-08', nationality:'Italy', weight:'63 kg', height:'1.78 m' }
  );

The UPDATE statement changes only the birthday field in the UDT:

UPDATE cycling.cyclist_stats SET basics.birthday = '2000-12-12' 
  WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;

Select the updated row:
```
SELECT * FROM cycling.cyclist_stats
  WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;;
```
The birthday value is changed, and the nationality, height, and weight values are unchanged:
Result

Insert or update UDT data

Insert or update an entire UDT record

Update individual fields in a UDT

Was this helpful?

Give Feedback