CODEWITHSUNDEEP
cassandra
iLikeKFC
iLikeKFC

Reputation: 199

Cassandra: update or insert map value together with other values

I have a table that looks like this:

(id, a, b, mapValue)

I want to update if exist or insert if not VALUES(id,a,b,mapValue). Where mapValue is the combination of the old mapValue with the new one, replacing the values of each key that was already there.

For example if the old mapValue was {1:c, 2:d} and the new one is {2:e, 3:f} the result would be {1:c, 2:e, 3:f}. I want to do this in a query that also updates/inserts id,a,b in VALUES(id,a,b,mapValue).

How can I achieve this? I've found this guide about updating maps but it doesn't say anything about updating them while dealing with other values in the table. I need to do this at the same time.

Upvotes: 1

Views: 2094

Answers (1)

Alex Ott
Alex Ott

Reputation: 87329

In Cassandra, there is no difference between INSERT & UPDATE - everything is UPSERT, so when you do UPDATE and data doesn't exist, it's inserted. Or if you do INSERT and data already exist, it will be updated.

Regarding map update, you can use + and - operations on the corresponding column when doing UPDATE. For example, I have a table:

CREATE TABLE test.m1 (
    id int PRIMARY KEY,
    i int,
    m map<int, text>
);

and I can do following to update existing row:

cqlsh:test> insert into test.m1 (id, i, m) values (1, 1, {1:'t1'});
cqlsh:test> select * from test.m1;

 id | i | m
----+---+-----------
  1 | 1 | {1: 't1'}

(1 rows)

cqlsh:test> update test.m1 set m = m + {2:'t2'}, i = 4 where id = 1;
cqlsh:test> select * from test.m1;

 id | i | m
----+---+--------------------
  1 | 4 | {1: 't1', 2: 't2'}

(1 rows)

and I can use the similar UPDATE command to insert completely new data:

cqlsh:test> update test.m1 set m = m + {6:'t6'}, i = 6 where id = 6;
cqlsh:test> select * from test.m1;

 id | i | m
----+---+--------------------
  1 | 4 | {1: 't1', 2: 't2'}
  6 | 6 |          {6: 't6'}

(2 rows)

Usually, if you know that no data existed before for given primary key, then UPDATE with + is better way to insert data into set or map because it doesn't generate a tombstone that is generated when you do INSERT or UPDATE without + on the collection column.

P.S. You can find more information on using collections in the following document.

Upvotes: 4

Related Questions