Feature Request: Alternative to ON DUPLICATE KEY UPDATE?

[vladd11]

Hello. I'd like to rewrite simple MySQL query:

INSERT INTO users(id, phone, sms_code, sms_code_expiration)
VALUES (?id, ?phone, ?sms_code, ?sms_code_expiration)
ON DUPLICATE KEY
UPDATE sms_code=?sms_code, sms_code_expiration=?sms_code_expiration;

I see that I need to execute 2 (or 1) requests to YDB (and use more Request Units) and I also need to handle exceptions on DB client. Pseudocode:

try:
    """
    DECLARE $id AS String;
    DECLARE $sms_code AS Uint32;
    DECLARE $sms_code_expiration AS Datetime;
    DECLARE $phone AS Utf8;

    INSERT INTO users(id, phone, sms_code, sms_code_expiration)
    VALUES ($id, $phone, $sms_code, $sms_code_expiration);
    """
except PreconditionFailed:
    """
    DECLARE $phone AS Utf8;
    DECLARE $sms_code AS Uint32;
    DECLARE $sms_code_expiration AS Datetime;

    UPDATE users 
    SET sms_code=$sms_code, sms_code_expiration=$sms_code_expiration
    WHERE phone=$phone;
    """

And I also need to make phone a primary key (because there aren't UNIQUE fields). But it's ok for me, if I want to change phone I can just remove field and add it again leaving previous ID. Yes, there are any duplicate chance on ID field (it's used to references) but it's pretty small (due to UUID4).

[dcherednik]

Hello. Probably UPSERT can help with your task. https://ydb.tech/en/docs/yql/reference/syntax/upsert_into

[vladd11]

Hello. Probably UPSERT can help with your task. https://ydb.tech/en/docs/yql/reference/syntax/upsert_into

No, it can't.

UPSERT INTO users(id, phone, sms_code, sms_code_expiration)
VALUES (?id, ?phone, ?sms_code, ?sms_code_expiration);

will update id column of row, but I need to do this only if row doesn't exist.

[fomichev3000]

UPSERT INTO users(id, phone, sms_code, sms_code_expiration)
VALUES (?id, ?phone, ?sms_code, ?sms_code_expiration);

will update id column of row, but I need to do this only if row doesn't exist. Technically, updating id with the same value leads to just updating values. To my mind result would be the same, either you call INSERT INTO ... ON DUPLICATE KEY <update all fields> or UPSERT ... <specify all fields>. Do you see any controversial example?

[uh-zuh]

INSERT INTO table1 (id, value, create_time)
VALUES ($id, $value, $create_time)
ON DUPLICATE KEY
UPDATE value = $value;

but UPSERT will modify create_time too.