Doing an upsert into a collection?

[Ali Akhtar <al...@gmail.com>]

Say I have this UDT:

*CREATE TYPE rating (*
* user text,*
* rating int*
*);*

And, I have this table:

*CREATE TABLE movie (*
* id text,*
* name text,*
* ratings list<FROZEN<rating>>,*
* PRIMARY KEY ( id )*
*);*

Say a user 'bob' rated a movie as a 5. Is it possible to do something like
this:

*UPDATE movie set ratings.rating = 5 WHERE ratings.user = 'bob'*

And have that query either update bob's previous rating if he had already
rated, or have it insert a new Rating into the ratings w/ user = bob,
rating = 5?

If not, can this be achieved with a map instead of a list?

Thanks.

Re: Doing an upsert into a collection?

[Michael Mior <mm...@uwaterloo.ca>]

You could do this with a map instead of a list.

*CREATE TABLE movie (*
* id text,*
* name text,*
* ratings map<text, int>,*
* PRIMARY KEY ( id )*
*);*

*UPDATE movie SET ratings['bob'] = 5 WHERE id = 'terminator 3';*

--
Michael Mior
michael.mior@gmail.com

2016-10-24 18:16 GMT-04:00 Ali Akhtar <al...@gmail.com>:

> Say I have this UDT:
>
> *CREATE TYPE rating (*
> * user text,*
> * rating int*
> *);*
>
> And, I have this table:
>
> *CREATE TABLE movie (*
> * id text,*
> * name text,*
> * ratings list<FROZEN<rating>>,*
> * PRIMARY KEY ( id )*
> *);*
>
> Say a user 'bob' rated a movie as a 5. Is it possible to do something like
> this:
>
> *UPDATE movie set ratings.rating = 5 WHERE ratings.user = 'bob'*
>
> And have that query either update bob's previous rating if he had already
> rated, or have it insert a new Rating into the ratings w/ user = bob,
> rating = 5?
>
> If not, can this be achieved with a map instead of a list?
>
> Thanks.
>

Re: Doing an upsert into a collection?

[kurt Greaves <ku...@instaclustr.com>]

On 24 October 2016 at 22:16, Ali Akhtar <al...@gmail.com> wrote:

> *UPDATE movie set ratings.rating = 5 WHERE ratings.user = 'bob'*


You won't be able to do this because you're trying to update a row without
specifying the primary key. Also, even if you did add the PK to the where,
you've specified a list of (frozen) ratings, so ratings.rating and
ratings.user doesn't make sense.

Collection types can't be part of the primary key, so updating as you've
mentioned above won't really be possible.

Kurt Greaves
kurt@instaclustr.com
www.instaclustr.com