Multiple records update into a column with inner join DB2

Question

I have written this update statement to update records in DB2.

UPDATE schemaname.tablename
SET fillerinfo = myfillerinfo
FROM schemaname.tablename,schemaname.tablename1
WHERE ID = uid1
AND PID=uuplanid
AND ACCTNO=uacctno

However, when running on AS400 DB2.

I have Data like this:

        Table1  | Table2
     fillerinfo |myfillerinfo
     -----------|-------------
         A      |   b
         c     |   D
       E       |   f

Like above i have 500K records.

Its showing error: Statement can not be executed.

I'm getting error:

Keyword FROM not expected. Valid tokens: USE SKIP WAIT WITH WHERE. Cause . . . . . : The keyword FROM was not expected here. A syntax error was detected at keyword FROM. The partial list of valid tokens is USE SKIP WAIT WITH WHERE. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

So I refereed this link to see if it helps: https://oscarvalles.wordpress.com/2013/05/12/db2-update-with-inner-joins/

But, this example in the above link is showing to update single records.

How can I update multiple records in a column with join? Please suggest.

add more detail like table structure and sample data if possible — Jayasurya Satheesh, Dec 15 '17 at 07:47

score 1 · Answer 1 · answered Dec 15 '17 at 14:32

DB2 is a bit different than MS SQL Server. You can update from a subselect like this

UPDATE schemaname.tablename
SET fillerinfo = (SELECT myfillerinfo
                  FROM schemaname.tablename,schemaname.tablename1
                  WHERE ...)
WHERE ID = uid1
AND PID=uuplanid
AND ACCTNO=uacctno

I suspect you can determine the correct WHERE clause in the subselect.

Jayasurya Satheesh · Answer 2 · 2017-12-15T10:05:56.063

Try INNER JOIN instead

UPDATE T1
SET 
fillerinfo = T2.myfillerinfo
FROM schemaname.tablename t1
INNER JOIN schemaname.tablename1 T2
ON T1.ID = T2.uid1
WHERE T1.PID=T2.uuplanid
AND T1.ACCTNO=T2.uacctno

Or try MERGE

MERGE INTO schemaname.tablename t1
    USING
    (
        SELECT
            uid1,
            uuplanid,
            uacctno,
            myfillerinfo 
            FROM schemaname.tablename1 
    )T2
        ON T1.ID = T2.uid1
    WHEN MATCHED AND T1.PID=T2.uuplanid
            AND T1.ACCTNO=T2.uacctno
        THEN UPDATE
            SET T1.fillerinfo = T2.myfillerinfo

OR try this

UPDATE schemaname.tablename T1
    SET 
        T1.fillerinfo = (SELECT T2.myfillerinfo FROM schemaname.tablename1 T2
                            WHERE T1.ID = T2.uid1
                            AND T1.PID=T2.uuplanid
                            AND T1.ACCTNO=T2.uacctno
                        )
        WHERE EXISTS
        (
            SELECT 1 FROM schemaname.tablename1 T2
                            WHERE T1.ID = T2.uid1
                            AND T1.PID=T2.uuplanid
                            AND T1.ACCTNO=T2.uacctno
        )

Have you tried this from iSeries navigator? Is this worked? I have updated my question with exact error. — AskMe, Dec 15 '17 at 09:14
I've used this type of query in SSMS. Please add the Table design to the question so that I can give more detailed answer — Jayasurya Satheesh, Dec 15 '17 at 09:16
Thanks for you answer. I'll check these queries. I have just refereed this : https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — AskMe, Dec 15 '17 at 11:55

Multiple records update into a column with inner join DB2

2 Answers2