How to use "ON DUPLICATE KEY UPDATE " for both row-store and column-store tables?

I am refering the below URL from MemSQL.

We are using MemSQL V7.0.When I try to create a stored procedure which you refer in the URL, I am receiving an error as below.

" SQL Error [1064] [42000]: Compilation error in function proc near line 8: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE tweet::retweeted_user_id is not null’ "

CREATE PROCEDURE proc(batch query(tweet json))
AS
BEGIN
INSERT INTO tweets(tweet_id, user_id, text)
SELECT tweet::tweet_id, tweet::user_id, tweet::text
FROM batch;

INSERT INTO retweets_counter(user_id, num_retweets)
  SELECT tweet::retweeted_user_id, 1
  FROM batch
  ON DUPLICATE KEY UPDATE num_retweets = num_retweets + 1
  WHERE tweet::retweeted_user_id is not null;

END;

If I remove the where condition, I am able to create the above stoed procedure successfully. Please clarify me the below things.

1)Why we are getting an error when we use “WHERE tweet::retweeted_user_id is not null” this?

2)Is there any limitations to use “ON DUPLICATE KEY UPDATE” for rowstore and column-store tables? If yes, how to achevie removing duplicates for a column-store table?

3)Give an example .

WHERE has to come before ON DUPLICATE KEY UPDATE.

ON DUPLICATE KEY UPDATE requires a unique key on the target table, and unique keys are only supported on rowstores.

It’s a priority for us to make unique keys and ON DUPLICATE KEY UPDATE work on columnstores in the future. For now, if the target table is a columnstore, you’d have to use another approach to do this conditional insert/update (upsert) with multiple statements.

It would be more helpful if you provide an example to us for a column-store table with upsert.

CREATE PROCEDURE proc(batch query(tweet json))
AS
BEGIN
INSERT INTO tweets(tweet_id, user_id, text)
SELECT tweet::tweet_id, tweet::user_id, tweet::text
FROM batch;

INSERT INTO retweets_counter(user_id, num_retweets)
  SELECT tweet::retweeted_user_id, 1
  FROM batch
WHERE tweet::retweeted_user_id is not null;
  ON DUPLICATE KEY UPDATE user_id = tweet::user_id
 

END;

When I use " ON DUPLICATE KEY UPDATE num_retweets = num_retweets + 1 " pipeline is running successfully but When I use " ON DUPLICATE KEY UPDATE user_id = tweet::user_id" I am getting an error.

Please guide us how to rectify it.

Kindly provide your update as soon as possible.

Try this:
INSERT INTO retweets_counter(user_id, num_retweets)
SELECT tweet::retweeted_user_id, 1
FROM batch
WHERE tweet::retweeted_user_id is not null;
ON DUPLICATE KEY UPDATE user_id = values(user_id);

Not sure if this is what you want though.

I’d be interested in learning if this feature is now available on ColumnStore as well given that 7.1 was released. Thanks!

Hi @christoph. What I said back on March 27th still holds. I can’t quote you a timetable though. Stay tuned!

Hi @hanson,

I thought that with 7.1 we now have unique keys kn columnstore as well (Columnstore Unique Keys Supported in MemSQL7.1), this I thought that maybe - given your post from March - this feature now also works in ColumnStore.

columnstore upsert is in 7.3