[SOLVED] Exasol Update Table using subselect

Exasol Update Table using subselect

I got this statement, which works in Oracle:

update table a set
a.attribute = 
    (select 
        round(sum(r.attribute1),4)
     from table2 p, table3 r
     where 1 = 1
     and some joins
    )                 
where 1 = 1
and a.attribute3 > 10 
;

Now I would like to do the same statement in Exasol DB. But I got error [Code: 0, SQL State: 0A000] Feature not supported: this kind of correlated subselect (Session: 1665921074538906818)

After some research, I found out you need to write the query in following syntax:

UPDATE table a 
set a.attribute = r.attribute2
FROM table a, table2 p, table3 r
     where 1 = 1
     and some joins
     and a.attribute3 > 10;

The problem is I can't take sum of r.attribute2. So I get unstable set of rows. Is there any way to do the first query in Exasol DB?

Thanks for help guys!

Solution

Following SQL UPDATE statement will work for cases if JOIN between table1 and table2 are 1-to-1 (or if there is a 1-to-1 relation between target table and resultset of JOINs)

In this case target table val column is updated otherwise an error is returned

UPDATE table1 AS a
    SET a.val = table2.val
FROM table1, table2
WHERE table1.id = table2.id;

On the other hand, if the join is causing multiple returns for single table1 rows, then the unstable error raised.

If you want to sum the column values of the multiplying rows, maybe following approach can help

First sum all rows of table2 in bases of table1 and use this sub-select as a new temp table, then use this in UPDATE FROM statement

UPDATE table1 AS a
    SET a.val = table2.val
FROM table1
INNER JOIN (
    select id, sum(val) val from table2 group by id
) table2
    ON table1.id = table2.id;

I tried to solve the issue using two tables In your case probably you will use table2 and table3 in the subselect statement

I hope this is the answer you were looking for