0

Is there any way to reference updatable table in UPDATE statement in Teiid (8.4)?

For example: i have table (tag_id, value, time, active_ind). I'd like to mark all tags with non-max time.

UPDATE BUFFER buf
SET active_ind = 'N';
WHERE "time" NOT IN (
    SELECT MAX("time")
    FROM BUFFER
    WHERE tag_id = buf.tag_id
);

But Teiid doesn't support alias for updatable BUFFER table. How can i make such update? Thanks in advance.

nidu
  • 549
  • 2
  • 18

1 Answers1

2

How about Teiid's MERGE INTO :

MERGE INTO 
BUFFER(tag_id,value,time,active_ind) 
(SELECT buf.tag_id,buf.value,buf.time,'N' FROM 
     (
     BUFFER AS buf 
     INNER JOIN 
     (SELECT tag_id, MAX("time") AS max_time FROM BUFFER GROUP BY tag_id) AS m 
     ON buf.tag_id=m.tag_id AND buf.time<m.max_time
     )
)

But as docs state:

requires the target table to have a primary key and for the target columns to cover the primary key

Hope that helps

Jan

newohybat
  • 176
  • 7