Tarantool Sql Update Join Support

Question

Is update join is supported in tarantool? I am checking tarantool sql reference but couldn't find any samples. When I try to use a query similar to one below, I get an error.

UPDATE
    COMMISSIONS
SET
    COMMISSIONS.COMMISSION = 
        COMMISSIONS.BASE_AMOUNT * TARGETS.PERCENTAGE
FROM 
    COMMISSIONS
    INNER JOIN TARGETS
        ON COMMISSIONS.TARGET_ID = TARGETS.TARGET_ID;

Here is the schema definition:

CREATE TABLE COMMISSIONS
(
    STAFF_ID    INTEGER PRIMARY KEY, 
    TARGET_ID   INTEGER, 
    BASE_AMOUNT INTEGER, 
    COMMISSION  INTEGER
);

CREATE TABLE TARGETS
(
    TARGET_ID  INTEGER  PRIMARY KEY, 
    PERCENTAGE INTEGER
);


INSERT INTO 
    TARGETS(TARGET_ID, PERCENTAGE)
VALUES
    (1,20),
    (2,30),
    (3,50),
    (4,60),
    (5,80);
    
   
INSERT INTO 
    COMMISSIONS(STAFF_ID, BASE_AMOUNT, TARGET_ID)
VALUES
    (1,100000,2),
    (2,120000,1),
    (3,80000,3),
    (4,900000,4),
    (5,950000,5);

And this is the error I get when I run the update command:

Caused by: org.tarantool.TarantoolException: Syntax error at line 1 near '.'
    at org.tarantool.TarantoolBase.serverError(TarantoolBase.java:31)
    at org.tarantool.TarantoolClientImpl.complete(TarantoolClientImpl.java:571)
    at org.tarantool.TarantoolClientImpl.readThread(TarantoolClientImpl.java:491)
    at org.tarantool.TarantoolClientImpl.lambda$startThreads$1(TarantoolClientImpl.java:238)
    at java.base/java.lang.Thread.run(Unknown Source)

PS: I am using dbeaver + tarantool jdbc driver to run queries if that's make any difference.

score 2 · Accepted Answer · answered Sep 21 '20 at 07:33

You can use a subquery, like this:

UPDATE COMMISSIONS SET COMMISSION = 
(
    SELECT COMMISSIONS.BASE_AMOUNT * TARGETS.PERCENTAGE 
    FROM TARGETS 
    WHERE COMMISSIONS.TARGET_ID = TARGETS.TARGET_ID
);

Our SQL dialect is derived from SQLite, so you may also check this relevant answer: Update with Join in SQLite

Tarantool Sql Update Join Support

1 Answers1