Unable to replace NULL values in a column with a string using Phoenix.

[#1]

There are about 10 million rows in our Phoenix table in which some of the rows one column has have NULL values or empty. We want to replace it with some meaning full String. We tried to run this query to replace the NULL or empty using the query

upsert into table1("column2") VALUES('xyz') where "column2" is null or ltrim(rtrim("column2")) = ''

However this is throwing Error: ERROR 602 (42P00): Syntax error. Missing "EOF" at line 1, column 60. (state=42P00,code=602). Request someone to point me to the right syntax to perform the above task.

If this is not possible with Phoenix , please guide on how this can be accomplished using hbase shell.

[#2]

Are you receiving an error? Can you show an example of a record you tried to modify, and how you were unable to modify it? Please provide more details...

[#3 · Accepted solution]

There are two types of upserts in Phoenix. One is upsert values and it doesn't support where clause. Another is upsert select.

You need the last one and it should look like

upsert into table1(id, "column2") select id,'replacing string' from table1 where "column2" is null and <other conditions>;

Don't forget that you must specify primary key values for any upsert statement (id in my example).

[#4]

Thank you very much for the help. We were able to replace NULL values with some meaning full string on about a million rows.

[#5]

If i am having more than one columns as primary key.How shall i proceed.

[#6]

The above syntax will help is replacing string on only row, how can I replace the string on like a million rows at a time without specifiyin primary key?