How to update Sphinx Realtime Index with field = old_value + new_value?

0
votes

I am trying to update the Sphinx Search Realtime Index , using values from my Mysql Table., I want to add the new value in the old value of the RT index's record like this , i want to achieve this 

UPDATE RT_index SET col1 = old_val + new_val WHERE id = xx ;

query i am trying is

UPDATE RT_index SET comments_count = comments_count + 3 WHERE id = 1157642

but Sphinx giving me errror

ERROR 1064 (42000): sphinxql: syntax error, unexpected IDENT, expecting CONST_INT (or 4 other tokens) near 'comments_count + 3 WHERE id = 1157642'

i have tried query like this 

 UPDATE RT_index SET comments_count = value(comments_count) + 3 WHERE id = 1157642;

but still sphinx gives error,

ERROR 1064 (42000): sphinxql: syntax error, unexpected IDENT, expecting CONST_INT (or 4 other tokens) near 'value(comments_count) + 3 WHERE id = 1157642'

How can i add new value in old value using update in sphinx real-time index ?

i am using PHP to do this.

there is not much info about it in http://sphinxsearch.com/docs/current.html#sphinxql-update

1
mysqlsphinxsphinxql

1 Answers

1
votes

You can't. Need to first run a SELECT query to get the current value, and then run the UPDATE.

Not sure if can use a transaction to make the update atomic.