How to fix "SQL Error Keyword FROM not expected" in db2?

Question

I have this query that I need to update

UPDATE pomhst hst
   SET hst.ponot1 = hdr.poshp1 
  FROM pomhdr hdr
 WHERE LENGTH(LTRIM(RTRIM(hst.poshp1))) > 0
   AND hdr.postat = 3;

and when I execute it, it shows this error

SQL Error [42601]: [SQL0199] Keyword FROM not expected. Valid tokens: USE SKIP WAIT WITH FETCH LIMIT ORDER WHERE OFFSET. I do not know what is wrong with my query

I tried adding inner join but it didnt work as well.

What's you db2 platform and version? – Mark Barinstein Mar 08 '23 at 13:04 — Mark Barinstein, Mar 08 '23 at 13:04

Barbaros Özhan · Answer 1 · 2023-03-08T14:13:23.683

1

Some Database Management System such as Oracle, DB2 doesn't support the syntax

UPDATE a_table SET .. FROM another_table unlike to some others such as Postgres.

You can alternatively use a MERGE statement as the below one :

MERGE INTO pomhst hst
     USING pomhdr AS hdr
        ON LENGTH(LTRIM(RTRIM(hst.poshp1)))  > 0 
       AND postat = 3
       AND hdr.id = hst.id
      WHEN MATCHED THEN UPDATE SET hst.ponot1 = hdr.poshp1

Demo

edited Mar 08 '23 at 14:13

answered Mar 08 '23 at 06:13

Barbaros Özhan

59,113
10
31
55

1

Also, it's a non-standard syntax. – jarlh Mar 08 '23 at 08:08
Thanks @jarlh you were right, just edited. – Barbaros Özhan Mar 08 '23 at 14:17
1

FWIW, Db2 also supports: `MERGE INTO pomhst hst USING pomhdr AS hdr ON hdr.id = hst.id WHEN MATCHED AND LENGTH(LTRIM(RTRIM(hst.poshp1))) > 0 AND postat = 3 THEN UPDATE SET hst.ponot1 = hdr.poshp1` – Lennart - Slava Ukraini Mar 09 '23 at 14:47

Jens · Answer 2 · 2023-03-19T19:31:31.373

1

You can use exists:

UPDATE pomhst hst SET hst.ponot1 = hdr.poshp1 
WHERE exists (select *  from FROM pomhdr hdr where LENGTH(LTRIM(RTRIM(hst.POSHP1)))  > 0 AND hdr.POSTAT = 3);

edited Mar 19 '23 at 19:31

answered Mar 08 '23 at 06:14

Jens

67,715
15
98
113

score 0 · Answer 3 · answered Mar 08 '23 at 06:14

DB2 still does not support joins in update statements, so you'll need to use MERGE;

OR, You can simply do it using a subquery as follows :

update pomhst hst 
set hst.ponot1 = (
    select hdr.poshp1 
    from pomhdr hdr 
    where hdr.ponum = hst.ponum and hdr.POSTAT = 3
) 
where LENGTH(LTRIM(RTRIM(hst.POSHP1))) > 0;

Here is a similar example.

How to fix "SQL Error Keyword FROM not expected" in db2?

3 Answers3