Skip to Content
Technical Articles
Author's profile photo Ajit Kumar Panda

#SAPCAP: Understanding CQL Queries (Node.js)


Query Language is an essential tool to communicate with a database to perform operations such as retrieving data, inserting data, updating data and deleting data. Query languages are designed to be concise and efficient, allowing users to specify complex operations with minimal effort. There are several types of query languages, including structured query language (SQL), which is the most common query language used in relational databases, and non-relational query languages such as MongoDB’s query language.

Simillarly SAP Cloud Application Programming Model provides a query language called CDS Query Language (in short CQL). In this blog post, I will try to explain my understandings and learnings about CQL. Let’s get started!!

Note: this blog post is written for CAP framework with Node.js runtime.

Query Styles

Primarily there are two ways or styles to construct and execute queries.
  1. SQL-Like fluent API provided by cds.ql
  2. Querying API provided by cds.service


Fluent API Examples

// retrieve data
await SELECT.from(Roots).where({ID:'38a13fed-f2d1-45bd-91ec-642053889c92'});

// create data
input = {name:'root 6', descr:'descr 6', dfield:'2023-02-11', tfield:'14:18:29',
         dtfield: '2022-02-11T04:08:29Z',tsfield: '2022-02-11T04:08:29.129Z',
         nfield: 290,afield: 23891100.123456};

await INSERT.into(Roots).entries(input);

Querying API Examples

// retrieve data

// create data
input = {name:'root 6', descr:'descr 6', dfield:'2023-02-11', tfield:'14:18:29',
         dtfield:'2022-02-11T04:08:29Z', tsfield:'2022-02-11T04:08:29.129Z',
         nfield: 290, afield:23891100.123456};

await cds.create(Roots).entries(input);
await srv.create(Roots).entries(input);


  • and – these methods are actually convenient shortcuts to the respective fluent API methods
  • In above example Roots is the entity and often referred as query target.It is recommended to use entity definitions reflected from a service’s model to construct queries instead of fully qualified enitty names with namespace. By utilizing this reflection technique, code can be simplified significantly as it eliminates the need to repeatedly include namespaces throughout the codebase.

    // using fully qualified entity names
    await SELECT.from('cap.cql.db.Roots')
    // using reflected entity definition
    const {Roots} = cds.entities;
    await SELECT.from(Roots)​
  • In all above mentioned api styles, arguments like columns of entity can be passed in multiple ways as shown below:

    // tagged template string
    await SELECT.from `cap.cql.db.Roots` .columns `name, descr as description`
    // cql column expressions
    const { Roots } = cds.entities;
    await SELECT.from (Roots) .columns ('name', 'descr as description')
    // cqn expression objects
    await SELECT.from `cap.cql.db.Roots` 
                .columns ({ref:['name']}, {ref:['descr'], as:'description'})
    // array of columns
    await SELECT.from `cap.cql.db.Roots` .columns (['name', 'descr as description'])
    // projection function
    await SELECT.from `cap.cql.db.Roots` 
                .columns (r => {,'description') }); ​
  • Projection functions is the recommended way as it supports nested projections aka expands, uses standard JavaScript constructs, offers type inference and code completion etc. However it does not support all functionalities. So sometimes, it might be required to use along with template string and other above mentioned mechanisms.

Schema Information for Sample Code


As shown in above picture, Roots is the main entity which has 4 child entities. AssocSItems and AssocMItems are associations to Roots with 1-to-1 and 1-to-N cardinality respectively. Simillarly CompSItems and CompMItems are compositions to Roots with 1-to-1 and 1-to-N cardinality respectively. Also Roots2 is another entity with more than one key.

All query examples are explained based on this schema. Also sample code is provided as git repository here: btp-cap-demo-usecases

Execute queries using Different DB

Executing queries to verify results before incorporating them into code is a common requirement. To facilitate this, the CAP framework offers the cds repl command, which enables live interaction with Node.js APIs and allows for query execution.

Following are the commands that you can use to test the queries provided in later part of this blog post.

  • Using Sqlite DB

    git clone
    cd cap-cql-examples
    npm install
    cds deploy --to sqlite
    cds repl
    await cds.test()
    const { Roots, AssocSItems, AssocMItems, 
            CompSItems, CompMItems, Roots2   } = cds.entities
  • Using HANA Cloud DB
    git clone
    cd cap-cql-examples
    npm install
    cds add hana --for hybrid
    cf login
    cds deploy --to hana:cap-cql-examples-db --profile hybrid --store-credentials
    cds repl --profile hybrid
    await cds.test()
    const { Roots, AssocSItems, AssocMItems, 
            CompSItems, CompMItems, Roots2  } = cds.entities
  • Better SqliteIn March 2023 release for the CAP framework, the SQLite Database Service has been rebuilt with a new architecture and the better-sqlite3 driver, resulting in significant features such as full support for path expressions, including infix filters and exists predicates, allowing for more advanced queries. Standard functions like contains, concat, and startswith are now supported in a database-agnostic way.SAP HANA functions like days_between and months_between are also supported, enhancing testing for SAP HANA-based production scopes. These improvements bring enhancements to both the feature scope and performance of the service.The new SQLite service will become the default in the upcoming major release.For more information, visit this page: New SQLite Service (beta)

    git clone
    cd cap-cql-examples
    npm install
    npm add @cap-js/sqlite -D
    jq '.cds = {requires:{db:"sql"}}' package.json > temp.json && mv temp.json package.json
    cds repl
    await cds.test()
    const { Roots, AssocSItems, AssocMItems, 
            CompSItems, CompMItems, Roots2  } = cds.entities​

    Note: You might need to install necessary command line tools like cf cli, jq, git etc.

Retrieve Data: Select Query Examples

SELECT queries are used to retrieve data from one or more tables of database.  Let’s look at few examples with different variations.

  • Retrieve full table data
    await SELECT.from(Roots);​
  • Retrieve single entry by one or more key

    // one key
    await SELECT.from(Roots).byKey('38a13fed-f2d1-45bd-91ec-642053889c92');
    await SELECT.from(Roots, '38a13fed-f2d1-45bd-91ec-642053889c92');
    // more than one key
    await SELECT.from(Roots2).byKey({first: 101, second:201});
    await SELECT.from(Roots2, {first: 101, second:201});
  • Retrieve data by one or more non-key field

    // string field
    await SELECT.from(Roots).where({name:'root 1'});
    await SELECT.from(Roots).where(`name like '%1%'`);
    await SELECT.from(Roots).where({category:{in:['rc1', 'rc2']}})
    await SELECT.from(Roots).where({category:{'not in':['rc1', 'rc2']}})
    await SELECT.from(Roots).where({category:['rc1', 'rc2']})
    // date field
    await SELECT.from(Roots).where({dfield:'2022-02-11'});
    await SELECT.from(Roots).where(`dfield < '2022-06-11'`);
    // time field
    await SELECT.from(Roots).where({tfield:'04:08:29'});
    await SELECT.from(Roots).where({tfield:{'>':'14:08:29'}});
    // datetime or timestamp field
    await SELECT.from(Roots).where(`dtfield < '2022-06-01T14:08:29Z'`);
    await SELECT.from(Roots).where(`tsfield > '2022-10-01T04:08:29.000Z'`);
    // numeric or amount field
    await SELECT.from(Roots).where(`nfield between 1 and 500`);
    await SELECT.from(Roots).where({nfield:{ between: 501, and: 1000}});
    await SELECT.from(Roots).where(`afield < 299345.451`);
    // more than one non-key field
    await SELECT.from(Roots).where({and: {dfield:'2022-10-01', nfield:123}});
    await SELECT.from(Roots).where(`dfield > '2022-10-01' and nfield > 500`);
    await SELECT.from(Roots)
                .where({and:{ dfield:{'>': '2022-10-01'}, nfield:{'>':600} }});​
  • Retrieve data by multiple conditions

    await SELECT.from(Roots).where(`nfield > 900 or nfield < 200`);
    await SELECT.from(Roots).where({or:{ nfield:{'>': 900}, nfield:{'<':200} }});​
  • Retrieve only few columns

    await SELECT.from(Roots).columns(['name','descr'])
    await SELECT.from(Roots).columns(r=>{, r.descr})
    await SELECT.from(Roots).where({name:{ like: '%root%'}})​
  • Retrieve associated and composition entity data
    // getting few column of associated and composition entity
    await SELECT.from(Roots)
                .columns(r=>{, r.descr, r.asitem(asi=>{asi.asi_sfield}) });
    await SELECT.from(Roots)
                .columns(r=>{, r.descr, r.csitem(csi=>{csi.csi_sfield}) })
    // getting all columns of associated and composition entity
    await SELECT.from(Roots)
                .columns(r=>{, r.descr, r.asitem(asi=>{asi`.*`}) })
    await SELECT.from(Roots)
                .columns(r=>{, r.descr, r.csitem(csi=>{csi`.*`}) })
    // getting all columns of root and associated entity
    await SELECT.from(Roots).columns(r=>{r`.*`,r.asitem(asi=>{asi`.*`}) })
    await SELECT.from(Roots).columns(r=>{r`.*`,r.amitems(ami=>{ami`.*`}) })
    await SELECT.from(Roots).columns(r=>{r`.*`,r.csitem(csi=>{csi`.*`}) })
    await SELECT.from(Roots).columns(r=>{r`.*`,r.cmitems(cmi=>{cmi`.*`}) })
    // mix and match
    await SELECT.from(Roots)
                .columns(r=>{, r.descr, 
                             r.amitems(ami=>{ami`.*`})       });
    await SELECT.from(Roots)
                .columns(r=>{, r.descr, 
                             r.cmitems(cmi=>{cmi.csm_nfield}) });
    //Use filter
    await SELECT.from `${Roots}[category='rc1']`
    await SELECT.from `${Roots}[category='rc1' and name like '%root%']`
    await SELECT.from(Roots)
                .columns(r=>{ r`.*`,
                              r.amitems`[asm_nfield > 1010]`(ami=>{ami`.*`}) })​

    SELECT.from(Roots).columns(r=>{r`.*`,r.cmitems(cmi=>{cmi`.*`}) })

    In above code snippet, The query is performed on the Roots entity using the SELECT.from method. The columns function is used to specify the desired columns in the query results. In this case, it selects all columns (.*) from the Roots entity. Additionally, it includes related cmitems entities using the nested callback function (cmi=>{cmi.*}), which indicates that all columns (.*) from the related cmitems entities are included in the query results.

    SELECT.from(Roots).columns(r=>{ r`.*`,r.amitems`[asm_nfield > 1010]`(ami=>{ami`.*`}) })

    In above code snippet, all columns (.*) from the Roots entity are selected, along with related amitems entities that meet the condition [asm_nfield > 1010]. The condition is called inflix filters. You can find more information here: infix-filters

  • Retrieve one entry or distinct entry

    //get one entry
                    .columns(r=>{r`.*`,r.amitems(ami=>{ami`.*`}) });
    //get distinct entry
    await SELECT.distinct.from(Roots)
  • Using functions and expressions
    // count function
    await SELECT.from(Roots).columns(`count(*) as totalrows`);
    // case statement
                .columns(`count(*) as totalcount`, 
                         `sum(CASE WHEN nfield < 500 THEN 1 END) as lowcount`, 
                         `sum(CASE WHEN nfield >= 500 THEN 1 END) as highcount`);
    // aggregate functions: average, minimum, maximun etc
    await`avg(nfield) as average`);
    await`min(nfield) as minimum`);
    await`max(nfield) as maximum`); 
    await SELECT.from(Roots).columns(`ceiling(afield) as afield`);
    // string function: concat, toupper, tolower
    await SELECT.from(Roots)
                .columns(`concat(name, ' ',descr) as name`, 
                         `length(name) as charcount`       );
    await SELECT.from(Roots)
                .columns(`toupper(name) as name`, `tolower(descr) as descr`)
    await SELECT.from(Roots)
                .columns(`year(dfield) as year`, 
                         `month(dfield) as month`, 
                         `day(dfield) as day`     );
    await SELECT.from(Roots)
                .columns(`hour(tfield) as hour`, 
                         `minute(tfield) as minute`, 
                         `second(tfield) as second` );​

    Let’s look at one of the above queries:

                           .columns( `count(*) as totalcount`,
                                            `sum(CASE WHEN nfield < 500 THEN 1 END) as lowcount`,
                                            `sum(CASE WHEN nfield >= 500 THEN 1 END) as highcount` );

    The function is used to create a query that fetches a single result row from the Roots entity. The .columns() method is used to specify the columns to be retrieved in the query result. In this case, it includes three columns:

    1. count(*) as totalcount: This column calculates the total count of records in the Roots entity.
    2. sum(CASE WHEN nfield < 500 THEN 1 END) as lowcount: This column calculates the sum of records where the nfield value is less than 500. It uses a conditional statement (CASE WHEN) to determine which records to include in the sum.
    3. sum(CASE WHEN nfield >= 500 THEN 1 END) as highcount: This column calculates the sum of records where the nfield value is greater than or equal to 500. Again, it uses a conditional statement to filter the records.
  • Group By and Order By

    await SELECT.from(Roots).columns(`name`).orderBy('name desc');
    await SELECT.from(Roots)
                .columns(`category`, `count(name) as itemscount`)
    await SELECT.from(Roots)
                .orderBy('category desc', 'name asc');
  • Limit by rows and offset

    // Only 2 entries are fetched
    await SELECT.from(Roots).limit(2).where(`name like '%root%'`)
    // Only 2 rows are fetched after 3 rows (offset)
    await SELECT.from(Roots).limit(2,3).where(`name like '%root%'`)​


  • Not every CQL or SQL statement can be expressed with projection functions. This is where tagged template strings kick in.
  • Template string and special character * is used to get all columns as shown above: r.`*`
  • Inflix filters are used to filter data. In last example, both template string and inflix filter are used to apply filter on child items: r.amitems`[asm_nfield > 1010]`(ami=>{ami`.*`})
  • Some of the query concepts are not supported by sqlite like path expressions etc. However it is suppoerted in both better sqlite and HANA db. Some examples are as below:
    await SELECT.from(Roots).columns('name',`asitem.asi_sfield`)
    await SELECT.from(Roots).columns(r=>{, r.descr, r.asitem.asi_sfield })
    await SELECT.from(Roots).columns(r=>{, r.descr, r.amitems.asm_sfield })
    await SELECT.from(Roots).where(`asitem.asi_nfield > 100`)
    cat = ['rc1', 'rc3']; 
    await SELECT.from(Roots)
                .where `category in ${cat} and amitems.asm_nfield > 1000`​

Create Data: Insert Query Examples

Insert queries are used to create entry in tables. Let’s look at few examples below:

  • Examples with Different Entities, Associations, Compositions

    // create one entry
    onerowdata = {name:'root 6', descr:'descr 6', dfield: '2022-04-12',
                  tfield:'04:08:29', dtfield: '2022-02-11T04:08:29Z',
                  tsfield:'2022-02-11T04:08:29.129Z', nfield: 200,
                  afield:'23891100.123456',category: 'rc3'};
    await INSERT.into(Roots).entries(onerowdata);
    // create one entry with associated entry
    onerowdata = {name:'root 7', descr:'descr 7', dfield: '2023-04-12',tfield: '14:08:29',
                  dtfield: '2023-02-11T04:08:29Z',tsfield: '2022-02-11T04:08:29.129Z',
                  nfield: 200,afield: '23891100.123456',category: 'rc3', 
    await INSERT.into(Roots).entries(onerowdata);
    one = {name:'root 7', descr:'descr 7', dfield:'2023-04-12', tfield:'14:08:29',
           dtfield:'2023-02-11T04:08:29Z', tsfield:'2022-02-11T04:08:29.129Z',
           nfield:200, afield: '23891100.123456',category: 'rc3', 
           amitems:[ {ID:'110f6a9f-b647-4e63-bdb2-03ec724d0d2b'},
                     {ID: '71bd1b1d-ca9c-403e-b435-99e58e8373c0'} ]};
    await INSERT.into(Roots).entries(one);
    // create one entry with composition entry
    one = {name:'root 8', descr:'descr 8', dfield:'2023-04-12', tfield:'14:08:29',
           dtfield: '2023-02-11T04:08:29Z',tsfield: '2022-02-11T04:08:29.129Z',
           nfield: 200,afield: '23891100.123456',category: 'rc3', 
           csitem:{csi_sfield:'csi field 7', csi_nfield:'700'} };
    await INSERT.into(Roots).entries(one);
    one = {name:'root 9', descr:'descr 9', dfield:'2023-01-12', tfield:'10:58:29',
           dtfield: '2023-12-11T04:08:29Z',tsfield: '2022-09-11T04:08:29.129Z',
           nfield: 400,afield: '13892100.723456',category: 'rc2', 
           cmitems:[ {csm_sfield:'csm field 01', csm_nfield:'100'}, 
                     {csm_sfield:'csm field 02', csm_nfield:'200'} ]};
    await INSERT.into(Roots).entries(one);
    // create multiple entry
    multi = [ {name:'root 20', descr:'descr 20', dfield:'2023-01-10', tfield:'18:08:29', 
               dtfield: '2023-02-11T04:08:29Z',tsfield: '2023-02-11T04:08:29.129Z',
               nfield: 400,afield: '23891100.123456',category: 'rc3'}, 
              {name:'root 21', descr:'descr 21', dfield:'2023-04-12', tfield:'16:08:29', 
               dtfield: '2023-02-11T04:08:29Z',tsfield: '2023-01-11T04:08:29.129Z',
               nfield: 300,afield: '23891100.123456',category: 'rc3'}];
    await INSERT.into(Roots).entries(multi);​
  • Different Variations

    // using columns and values
    await INSERT.into(Roots)
                .values('root 10','descr 10','2022-04-12','04:08:29','2022-02-11T04:08:29Z',
    // using columns and rows
    await INSERT.into(Roots)
                .rows(['root 11','descr 11','2023-04-12','04:08:29','2022-02-11T04:08:29Z',
                      ['root 12','descr 12','2023-04-12','04:08:29','2022-02-11T04:08:29Z',
                      ['root 13','descr 13','2023-01-12','04:08:29','2022-02-11T04:08:29Z',

Create or Modify Data: Upsert Query Examples

Upsert queries are used to create or modify entries in tables. To modify, all key fields need to be part of the data. Let’s look at few examples below:

  • Different Variations
    // create entry
    await UPSERT.into(Roots)
          .entries({name:'root 6', descr:'descr 6', dfield:'2022-04-12', 
                    tfield:'04:08:29', dtfield: '2022-02-11T04:08:29Z',
                    tsfield: '2022-02-11T04:08:29.129Z', nfield: 200, 
                    afield: '23891100.123456', category: 'rc3'});
    // create entry with association
    await UPSERT.into(Roots)
          .entries({name:'root 7', descr:'descr 7', dfield:'2023-04-12', 
                    tfield:'14:08:29', dtfield:'2023-02-11T04:08:29Z', 
                    tsfield:'2022-02-11T04:08:29.129Z', nfield:200, 
                    afield:'23891100.123456', category:'rc3', 
    // modify entry
    await UPSERT.into(Roots)
                .entries({name:'root 11',ID:'7891fd0d-5924-471a-89c4-519f7df071b8'});
    await UPSERT.into(Roots)
          .entries({name:'root 66', descr:'descr 6', dfield:'2022-04-12', 
                    tfield:'04:08:29', dtfield:'2022-02-11T04:08:29Z', 
                    tsfield: '2022-02-11T04:08:29.129Z', nfield:200, 
    // modify root entry with creation of composed entry: Not working
    await UPSERT.into(Roots)
          .entries({ID: '7891fd0d-5924-471a-89c4-519f7df071b8', descr: 'descr 1111', 
                    amitems: [{asm_sfield: 'asm string 1111',asm_nfield: 1001,
                               root_ID: '7891fd0d-5924-471a-89c4-519f7df071b8'}] })​



  • Upsert for CAP Node.js does not (unlike CAP Java) support deep payloads.

Update Data: Update Query Examples

Update queries are used to modify data in a table. Here are a few examples:

  • Using Set or With: Different Variations

    // by single key
    await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
                .set({name:'root 333'});
    await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
                .set({name:'root 332',dfield: '2021-12-20'});
    await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
                .set({nfield: {'-=': 89}});
    await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
                .set(`nfield = nfield * 2`);
    // by multiple key
    await UPDATE.entity(Roots2,{first:101,second:201}).set({name:'r2 name1 extra'});
    // by cqn expression
    cqnExpression = {xpr: [{ref:['descr']}, '||', {val:'Additional Description'}]};
    await UPDATE.entity(Roots2,{first:102,second:202})
                .with({descr: cqnExpression})
    // by functions with template string
    await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
                .set(`descr = concat('descr',' add')`);
    await UPDATE.entity(Roots2, {first:102,second:202})
                .set(`descr = concat('descr',' add'), name = 'root 33'`);
    // by where clause
    await UPDATE.entity(Roots)
                .set({nfield: {'-=': 89}})
    await UPDATE.entity(Roots2)
                .set({name: 'r2 name2 new'})
                .where(`descr like '%Add%' and first = 102`);​

    UPDATE.entity(Roots, ‘f11aacb1-ab35-40da-918b-589cdd387ad1’).set(`nfield = nfield * 2`)

    The UPDATE.entity method is used to update the Roots entity with the specified ID ‘f11aacb1-ab35-40da-918b-589cdd387ad1’. The .set() method is used to specify the changes to be made in the update operation. In this case, it sets the nfield column to be multiplied by 2. You can look at different examples as provided above.


  • When update query executes successfully, it returns no of rows updated by the query.
  • If all keys are passed as part of query then only one entry of the entity is updated.
  • Method .set and .with are aliases to the same method.

Delete Data: Delete Query Examples

Delete queries remove data from tables. Take a look at the examples below:

  • Different Variations

    // using one or more key
    await DELETE.from(Roots,'445b1357-90b2-4a2c-a60d-7f7e40d46f12');
    await DELETE.from(Roots2,{first:101, second:201});
    // using where condition
    await DELETE.from(Roots).where({category:'rc1'});
    await DELETE.from(Roots2).where({first:101})
    await DELETE.from(AssocMItems).where({asm_nfield:{'>=':1014}});​


Note: This blog post does not explore into the usage of Native HANA features. However, if you’re interested in learning more about this topic, you can visit this page: Native Features


In this blog post, We explored how CQL can be used to execute database operations such as retrieving data, inserting data, updating data and deleting data.

More information about cloud application programming model can be found here. You can follow my profile to get notification of the next blog post on CAP. Please feel free to provide any feedback you have in the comments section below and ask your questions about the topic in sap community using this link.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah

      Well described and explained Ajit Kumar Panda !

      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda
      Blog Post Author

      Thank you Yogananda Muthaiah !

      Author's profile photo Jason Scott
      Jason Scott

      What an awesome blog post Ajit Kumar Panda pulling out all the useful CQL information into one place. A very handy reference !!

      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda
      Blog Post Author

      Thank you Jason Scott for reading the blog post. 😊

      Author's profile photo Swati Maste
      Swati Maste

      Great compilation of CQL queries Ajit Kumar Panda ! This summary is a time-saver ! Looking forward to more helpful content from you!

      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda
      Blog Post Author

      Thank you Swati Maste for reading the blog post 😊

      Author's profile photo Mohit Bansal
      Mohit Bansal

      What an amazing blog Ajit. All the required information at one place with detailed example.kudos


      Mohit Bansal

      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda
      Blog Post Author

      Thank you Mohit Bansal for reading. I am glad that you liked it. 😊

      Author's profile photo Thomas Nelissen
      Thomas Nelissen

      Really good and comprehensive blog post Ajit! Thanks for sharing!

      Would it be possible to add a section about JOIN conditions 😇?

      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda
      Blog Post Author

      Hi Thomas Nelissen ,

      Thank you for reading the blog post.

      Join conditions?  If tables are connected via association or composition then you dont need join conditions. You can fetch it without rewriting the join conditions. However if you are talking about new join conditions, can you give an example or schema reference. i can give it a try and update the blog if required.

      BR, Ajit

      Author's profile photo Thomas Nelissen
      Thomas Nelissen

      Ok, you caught me in not reading the entire blog post 😇

      I have been working with CAP for some time now but had no idea that this was possible:

      await SELECT.from(Roots).columns(r=>{r`.*`,r.asitem(asi=>{asi`.*`}) })


      Typescript is not very happy with it, but we'll find our way around 😊

      Thanks again for this great overview of CQL queries!

      Author's profile photo Rohit Makkar
      Rohit Makkar

      Hi Ajit,

      Thanks for wonderful blog. Can we update multiple lines using update query. We are able to insert/upsert multiple lines but could not find any option to update multiple lines.

      Author's profile photo Ajit Kumar Panda
      Ajit Kumar Panda
      Blog Post Author

      Hi Rohit Makkar,

      You can use where conditions.

      // by where clause
      await UPDATE.entity(Roots)
                  .set({nfield: {'-=': 89}})

      Let's look at the above example. If the where condition results in more than 1 rows then above query will update all relevant rows and in return it returns how many rows got updated.

      Best Regards, Ajit