CODEWITHSUNDEEP
apache-phoenix
Alchemist
Alchemist

Reputation: 869

How to use WHERE clause with Phoenix UPSERT

Looks like UPSERT doesn't support a WHERE clause.

But people have used workaround like following:

UPSERT INTO test.MyTable(col1, col2) SELECT col3, col4 FROM test.TempTable WHERE col5 = ABC

Does that mean if I have a use case where I need to update all the columns of the MyTable based on some key, I need to add the data to be updated in another TempTable then use a command similar to above to update data of the MyTable. Please let me know if my understanding is correct. Somehow adding data in another table just to update source table does not seems correct.

Upvotes: 0

Views: 848

Answers (1)

Naman Kumar
Naman Kumar

Reputation: 1

UPSERT SELECT
Inserts value if the value is not already present and updates the rows in the table based on the results of running the next query

UPSERT INTO test.targetTable(cq1, cq2) SELECT cq3, cq4 FROM test.sourceTable WHERE cq5 < 100

keep in mind cq3, cq4 one of the should be primary key/composite key, so as phoenix can understand on which rows you want to update

ref: https://docs.cloudera.com/runtime/7.3.1/phoenix-commands/topics/phoenix-command-reference.html

Upvotes: 0

Related Questions