learnkdb
Back to Update

qSQL · Update · Practice Question

Create a Boolean Flag Column

Sign in to track your progress

Question

Given the following table:

t:([] sym:`A`B`A`C`B`A`C`B`A`C;
      price:120.5 151.2 175.0 160.4 149.9 180.3 130.0 155.5 99.8 201.1)

Result:

sym price
---------
A   120.5
B   151.2
A   175
C   160.4
B   149.9
A   180.3
C   130
B   155.5
A   99.8
C   201.1

Update the table to add a new column called highPrice, such that:

  • highPrice = 1b when price > 150.0
  • highPrice = 0b otherwise

Expected result:

sym price highPrice
-------------------
A   120.5 0
B   151.2 1
A   175   1
C   160.4 1
B   149.9 0
A   180.3 1
C   130   0
B   155.5 1
A   99.8  0
C   201.1 1

Solution

Method 1

update highPrice:1b from `t where price>150.0

Method 2

update highPrice:price>150.0 from `t

Explanation

Using a Conditional Update

The statement:

update highPrice:1b from `t where price>150.0

updates only the rows that satisfy the condition:

price>150.0

Rows that are updated receive 1b. Because highPrice is a new column, the remaining rows are filled with the null value for its type; booleans have no null, so they get 0b.

Result:

sym price highPrice
-------------------
A   120.5 0
B   151.2 1
A   175   1
C   160.4 1
B   149.9 0
A   180.3 1
C   130   0
B   155.5 1
A   99.8  0
C   201.1 1

Using a Boolean Expression

A more direct approach is:

update highPrice:price>150.0 from `t

The expression:

price>150.0

returns a boolean vector:

0111010101b

which is assigned directly to the new column.

This approach is often preferred because it clearly expresses the intended logic and avoids the need for a separate where clause.

Summary

Two common approaches are:

update highPrice:1b from `t where price>150.0

and

update highPrice:price>150.0 from `t

The second form is generally more concise and is a common q idiom for creating boolean flag columns from a condition.