https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107443#M54604 <P>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?</P> Thu, 16 Feb 2017 17:40:49 GMT akash_sabarad 2017-02-16T17:40:49Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107440#M54601 <P>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 </P><P>upsert into table1("column2") VALUES('xyz') where "column2" is null or ltrim(rtrim("column2")) = ''</P><P>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.</P><P>If this is not possible with Phoenix , please guide on how this can be accomplished using hbase shell.</P> Thu, 16 Feb 2017 00:17:07 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107440#M54601 akash_sabarad 2017-02-16T00:17:07Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107441#M54602 <P>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...</P> Thu, 16 Feb 2017 00:25:45 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107441#M54602 elserj 2017-02-16T00:25:45Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107442#M54603 <P>There are two types of upserts in Phoenix. One is <STRONG>upsert values</STRONG> and it doesn't support where clause. Another is <STRONG>upsert select.</STRONG></P><P>You need the last one and it should look like </P><PRE>upsert into table1(id, "column2") select id,'replacing string' from table1 where "column2" is null and &lt;other conditions&gt;;</PRE><P>Don't forget that you must specify primary key values for any upsert statement (id in my example).</P> Thu, 16 Feb 2017 01:57:46 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107442#M54603 ssoldatov 2017-02-16T01:57:46Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107443#M54604 <P>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?</P> Thu, 16 Feb 2017 17:40:49 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107443#M54604 akash_sabarad 2017-02-16T17:40:49Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107444#M54605 <P>Thank you very much for the help. We were able to replace NULL values with some meaning full string on about a million rows.</P> Thu, 16 Feb 2017 18:47:03 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/107444#M54605 akash_sabarad 2017-02-16T18:47:03Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/286584#M54606 <P>If i am having more than one columns as primary key.How shall i proceed.</P> Mon, 30 Dec 2019 13:53:15 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Unable-to-replace-NULL-values-in-a-column-with-a-string/m-p/286584#M54606 mayank_tripathi 2019-12-30T13:53:15Z