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?
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.
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.
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);