SAP HANA update with join

Question

Im trying to do an UPDATE on a HANA DB with a Join. An online SQL Checker shows a Valid Syntax and after i found this: https://answers.sap.com/questions/11364926/sql-update-join-error.html i changed the order too of my update. It still gives this error:

sql syntax error: incorrect syntax near "LEFT": line 3 col 1 (at pos 119)

The Update looks like this (censored cause it includes some Data from my Company):

UPDATE TABX AS a
SET b.att1 = 'XXX', b.att2 = 'ZZZ'
LEFT JOIN TABZ AS b ON a.att3 = b.att3
WHERE a.att4 LIKE 'YYY'
AND att5 = 'PPP'
AND is_valid = 'TRUE'

According to the Post on SAP Answers it should work. And i know i did this on previous SQL-Databases, but HANA is now the First time.

How do i solve this?

It looks like you are trying to update 'TABX AS a' but the SET operations are being done on columns of table b. — Preetham Solomon, Oct 20 '22 at 23:30
Does this answer your question? [Update 2 joined tables simultaneously on HANA?](https://stackoverflow.com/questions/63710787/update-2-joined-tables-simultaneously-on-hana) — Suncatcher, Oct 21 '22 at 00:52
Please clarify your question, it's unclear if you want to update TABX (a) or TABZ (b), as already said yesterday. — Sandra Rossi, Oct 22 '22 at 13:55
@SandraRossi I didnt know which table i needed to update. I took the Select and changed it to an Update for that case. — Schesam, Oct 22 '22 at 20:00
You don't know which table to update. How is that possible that you don't know if it's to be `UPDATE tabx` or `UPDATE tabz`? I don't get it. Ask your colleagues to see who knows what table is to be updated. — Sandra Rossi, Oct 23 '22 at 13:16

Mathias Kemeter · Accepted Answer · 2022-10-21T07:07:50.170

For sure, the syntax is incorrect as can be seen in the documentation (note that there is no JOIN keyword).

However, the solution depends on the HANA version, that you are using.

HANA2 on-premise: You can use the UPDATE... SET... FROM... WHERE... syntax (see documentation incl. example). In the FROM part you essentially do a cross-join and make it a left join using the WHERE condition.
HANA Cloud: In HANA Cloud the FROM clause of the UPDATE statement has been omitted in favor of the more powerful (yet more complex) MERGE INTO statement (see documentation).

Note, that the MERGE INTO statement also exists for HANA2 on-premise (see documentation), which is why its use is probably preferrable.

Your example using the simplified syntax should look like something similar to this:

UPDATE TABZ SET att1 = 'XXX', att2 = 'ZZZ'
FROM TABZ b, TABX a
WHERE 
    -- LEFT JOIN
    -- (pls check if this should actually be an inner join)
    (a.att3 = b.att3 OR b.att3 IS NULL)
    -- your other conditions
    AND a.att4 LIKE 'YYY' AND att5 = 'PPP' AND is_valid = 'TRUE'

Note that in any case, you need to use UPDATE TABZ as you ultimately update columns from that table (and not TABX).

SAP HANA update with join

1 Answers1