CODEWITHSUNDEEP
kdb+
Shankar
Shankar

Reputation: 133

KDB Query to update a column with list

I need to update a column with a list which has just one symbol. Below one is not working. Note that city is a column which takes list of symbols.

update city: enlist `Lodnon from `user where id in (1,2,3);

Upvotes: 1

Views: 1507

Answers (4)

nikeros
nikeros

Reputation: 3379

Alternatively you could use the ?[boolean_list;if_true;if_false] operator as follows:

/ this is just to create a test table
t:([] id:(1;2;3;4); city:4#`)

/ check if each element of t`id is in (1;2;3)
/ 1) `London if true, ` if false
/ 2) assign t[`city]
t[`city]:?[in[t`id;(1;2;3)];`London;`]

Upvotes: 0

Shankar
Shankar

Reputation: 133

I was able to do it with the following.

update city: 3#enlist enlist `Lodnon from `user where id in (1,2,3);

Upvotes: -1

kylebonnes
kylebonnes

Reputation: 936

You current query is very close. You want to provide an atom (i.e. `london ) rather than a list:

update city:`london from user where id in 1 2 3

The query you suggested will fail with a length error (you're providing a list with 1 element to replace 3 column entries, assuming there are 3 records with id in 1 2 3).

Edit: To have the actual column values enlisted, you will have to make sure that the city column takes lists (i.e. applying the keyword meta to your table should return a capital S in the t column for city.

Assuming your city column currently has a small s when running meta user, you can update this by running:

update city:enlist each city from `user

Then, the following will make the change you desired:

update city:city:\:enlist`london from user where id in 1 2 3

Upvotes: 4

Michael K
Michael K

Reputation: 226

If you want it to remain as a list you could do something like

{update city:(x)#enlist `london from `user where id in 1 2 3}count select from t where id in 1 2 3

Or to tidy this up, you could use

update city:count[i]#enlist `London from t where id in 1 2 3

As is the i in the count will be the same length as the list due to filtering in the where clause.

Upvotes: 1

Related Questions