How to update from a temporary table? FROM clause not working

I'm trying to update table1.logged_off (a LONGDATE column) using data from a local temporary table with matching IDs. Here's what I have so far:

Here's what I'm doing:

Step 1: Create and fill the temporary table

CREATE LOCAL TEMPORARY TABLE #temp_fix (
    id NVARCHAR(32) NOT NULL,
    logged_off LONGDATE
);

-- I tried bulk insert:

INSERT INTO #temp_fix (id, logged_off) VALUES
('42010AEEC7541EEAB096F9B215BD755C', TO_TIMESTAMP('2025-03-03 12:01:59.000000000')),
('42010AEEC7541EDAB79F1E88AA390C02', TO_TIMESTAMP('2025-03-03 13:56:59.000000000'));

The insert gives an error unless I split it into multiple single-row inserts. Is this a limitation in SAP HANA, or am I missing syntax?

Step 2: Try to update the main table from the temp table

UPDATE table1 AS t
SET t.logged_off = f.logged_off
FROM #temp_fix AS f
WHERE t.id = f.id;

This gives a syntax error (incorrect syntax near FROM). I know this works in other RDBMS (like SQL Server), but what's the correct way to do this in SAP HANA?

Notes:

table1 is a regular table
logged_off is of type LONGDATE How can I properly perform this update using a temporary table?

Solution

The insert gives an error unless I split it into multiple single-row inserts. Is this a limitation in SAP HANA?

Yes. You can use a parameterized statement and bind multiple rows of parameters to it through your client (this is much more efficient than generating a SQL with n inserts):

INSERT INTO #temp_fix (id, logged_off) VALUES (?, ?);

Try to update the main table from the temp table … This gives a syntax error (incorrect syntax near FROM)

For HANA Cloud, UPDATE FROM is not supported, instead MERGE INTO should be used (as this is standard ANSI SQL, UPDATE FROM is not standardized):


MERGE INTO table1 USING #temp_fix
  ON #temp_fix.id = table1.id
  WHEN MATCHED THEN UPDATE SET table1.logged_off = #temp_fix.logged_off;

For HANA2, UPDATE FROM is still supported (but MERGE INTO might still be the better choice), though be aware of the semantic differences between versions documented in SAP Note 2241598.