question

mishra.anurag643_153409 avatar image
mishra.anurag643_153409 asked smadhavan edited

What does "last component of primary key" mean when using the IN operator?

if I need to update certain column with some value and this change needs to be done on multiple rows in that case I can use IN operator with where clause . But I was going through documentation and there is mentioned as below :

" To specify more than one row, use primary_key_name IN ( primary_key_value, primary_key_value … ). This only works for the last component of the primary key."

on above I understand we need to use primary key IN operator , but I am confused with "This only works for the last component of the primary key."

what does above line indicate here ?

[EDIT] Sample schema:

CREATE TABLE killrvideo.poc (
    id int,
    address text,
    name text,
    age text,
    education text,
    status text,
    PRIMARY KEY ((id, address), name, age)
) WITH CLUSTERING ORDER BY (name ASC, age ASC);
cqlsh:killrvideo> select * from poc;
 id | address | name | age | education | status
----+---------+------+-----+-----------+--------
  4 | satna   | RM   | 23  | BE        | Single
  1 | satna   | AM   | 27  | BE        | Single
  2 | satna   | RT   | 31  | BE        | Single
  3 | Pune    | AK   | 27  | BE        | Single

update command :

update poc set status = 'M' where age in ('27','31');

error :

InvalidRequest: Error from server: code=2200 [Invalid query] message="Some partition key parts are missing: id, address"
cql
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered smadhavan edited

A primary key can be composed of:

  • a partition key column for a simple primary key
  • a composite partition key
  • a partition key + clustering column(s) for a compound primary key

Here are some examples:

SIMPLE PRIMARY KEY

    PRIMARY KEY (email)

COMPOSITE PARTITION KEY

    PRIMARY KEY ((title, year))

COMPOUND PRIMARY KEY

    PRIMARY KEY (stock_id, exchange_time)
    PRIMARY KEY ((album, year), track_number)

Where the primary key only contains a partition key, the last component is just the partition key column. For a compound primary, the last component is the last clustering column in the key. Cheers!

[UPDATE] The issue with your query is that you haven't specified all the columns in the primary. As the error states, there are parts of the primary key missing namely id and address.

You need to specify the partition key you are updating plus all the clustering columns to identify the row in the partition.

The valid query is:

UPDATE poc
  SET status = ?
  WHERE id = ? AND address = ? AND name = ? AND age IN (...);
12 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

mishra.anurag643_153409 avatar image mishra.anurag643_153409 commented ·

I am clear with concepts explained above , my question is more specific related to update command for multi rows with IN operator when there is compound primary key .can you please illustrate with one example for update multiple rows when primary key is compound primary key .

0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ mishra.anurag643_153409 commented ·

I'm not sure which bit is unclear. Can you post the link to the document you were referring to?

0 Likes 0 ·
mishra.anurag643_153409 avatar image mishra.anurag643_153409 Erick Ramirez ♦♦ commented ·

https://docs.datastax.com/en/cql-oss/3.x/cql/cql_reference/cqlUpdate.html

1608206796692.png

0 Likes 0 ·
1608206796692.png (45.1 KiB)
Show more comments
Erick Ramirez avatar image Erick Ramirez ♦♦ mishra.anurag643_153409 commented ·

I've updated my answer to take into account the sample schema and query you provided. Cheers!

0 Likes 0 ·