Update with join

[Scott Green]

Hi, I'm having trouble with converting some existing MSSQL database procedures to HSQL. I can't seem to get my update with a join to work. Here's the MSSQL:

UPDATE A SET EndTime = V_EndTime FROM TableA A
INNER JOIN TableB B on B.ID = A.ID
WHERE B.DBID = V_DBID AND A.StartTime < V_EndTime;

V_EndTime and V_DBID are values passed in to the proc.

I gather that you can't have a FROM in an UPDATE statement in HSQL, so I've been trying various other permutations but without any luck. Any help would be appreciated.

[Scott Green]

I think I've got a solution, although it may not be very efficient:

UPDATE TableA A SET A.EndTime = V_EndTime
WHERE A.StartTime < V_EndTime AND
ID IN (SELECT ID FROM TableB B WHERE B.DBID = V_DBID);

That seems to work, but I wonder could it be improved?

[Fred Toussi]

You can rewrite this as a MERGE statement that has only a WHEN MATCHED UPDATE clause.

[Scott Green]

Hi Fred thanks for the help. I've now got a MERGE which is doing what I need:

MERGE INTO TableA USING
(SELECT ID FROM TableA AS H
JOIN TableB AS C ON H.LINK_ID = C.LINK_ID
WHERE H.EndTime is NULL
AND C.DBID = V_DBID
GROUP BY ID)
AS vals(x) ON TableA.ID = vals.x
WHEN MATCHED THEN UPDATE SET TableA.EndTime = V_EndTime;