SELECT from data change table

jzhardy · Posted: Thu Apr 20, 2023 6:51 am

this is posted more out of academic interest, rather than trying to address a specific need.

Is there any formulation for combining "OLD TABLE" and "FINAL TABLE" in the one SQL. I suspect not, but there are situations where it might be beneficial, eg where the cost of evaluating the WHERE clause is expensive.

my approach would be to write two SQLs such as :

SELECT "before update =>", col1,col2 .. from OLD TABLE
(
update <target table>
... where (<some expensive clause > )
);

SELECT "after update =>", col1,col2 .. FROM <target table> WHERE <expensive clause>
;

assume of course that the target_table is eligible for "FINAL TABLE" evaluation - ie, has no associated triggers.

jzhardy · Posted: Thu Apr 20, 2023 7:25 am

I think I worked it out ... I can simply use the INCLUDE modifier within the UPDATE.

eg:

SELECT old_val, Col1
FROM FINAL TABLE (
UPDATE <target table>
INCLUDE (OLD_VAL CHAR(4))
SET <target col> = 'XXXX'
,OLD_VAL = <target col>
WHERE <expensive clause>
);

Rohit Umarjikar · Posted: Fri Apr 21, 2023 5:00 pm

I couldn’t follow the problem or issue they made you to write this sql. If there is a performance issue then many ways it can be improved without changing the query but we don’t know yet the actual problem .

jzhardy · Posted: Mon Apr 24, 2023 7:40 am

just to repeat: this was only asked out of academic interest.

I can't actually think of a real-world scenario where this would be significantly beneficial. If the cost of of the WHERE predicate was high, then probably better to write SQL to generate an update-SQL (to apply update on full primary key), and then simply run this (generated) SQL.

Some shops do have a standard for UPDATE SQL that requires pre- and post- value to be reported in full. For this it does offer a slightly more compact and efficient path. For readability though I would go for two statements , eg:

select 'before update ==>',OT.* from XXX OT where YYY;
select 'after update ==>', FT.* from FINAL TABLE(update XXX where YYY) FT;

jzhardy · Posted: Mon Apr 24, 2023 7:56 am

possibly another use case would be if the transaction was bound at CS, but we want to keep the update statement and the post-select 'in step'. A bit contrived, because we could bind at RR, or just lock down the whole table and run the update/report transaction.

Rohit Umarjikar · Posted: Tue Apr 25, 2023 10:09 am

I wouldn’t go fancy much as simplest is best for maintenance purpose for anyone else using taking over the support.

However thanks for sharing the idea.