How can you access individual values in the ON DUPLICATE KEY UPDATE section of an INSERT INTO statement in SurrealDB?

Question

In SQL you can access it with the VALUES operator in the ON DUPLICATE KEY UPDATE as shown here:

INSERT INTO users (id, username, email)
VALUES (1, 'new_user', 'new@example.com')
ON DUPLICATE KEY UPDATE
    email = VALUES(email);

Is there a way to do the same in SurrealDB?

I tried to do it with the VALUES operator as you can in SQL. I was however not able to get this to work.

score 0 · Answer 1 · answered Aug 24 '23 at 13:52

0

Yes, You can see the example from the Insert Statement documentation:

INSERT INTO product (name, url) VALUES ('Salesforce', 'salesforce.com') ON DUPLICATE KEY UPDATE tags += 'crm';

So your Insert statement would look like:

INSERT INTO users (id, username, email)
VALUES (1, 'new_user', 'new@example.com')
ON DUPLICATE KEY UPDATE 
    email = 'newemail@example.com';

answered Aug 24 '23 at 13:52

naisofly

Allow me to clarify my request further. I'm aiming to retrieve the values from the current iteration of the `VALUES (...)` clause. Here is a more detailed version of my example: ```sql INSERT INTO users (id, username, email) VALUES (1, 'new_user', 'new@example.com'), (2, 'new_user2', 'new2@example.com') ON DUPLICATE KEY UPDATE email = VALUES(email); ``` Here I'm interested in extracting the email values, specifically 1. iteration "new@example.com" and 2. iteration "new2@example.com" The challenge lies in accessing individual values from each set of values being inserted. – Damgaard Aug 25 '23 at 11:20

1 Answers1