0

i have got this Select SQL working

SELECT O.* , S.NEW_EOC_CNTRT_SQ
FROM  FAMSUNIT.EOCT025_OPP_WRK O INNER JOIN
(
    SELECT A.EOC_CNTRT_SQ AS OLD_EOC_CNTRT_SQ, (SELECT B.EOC_CNTRT_SQ  
                FROM FAMSUNIT.EOCT020_CNTRT B 
                WHERE  B.ALIAS_CNTRT_ID = A.ALIAS_CNTRT_ID 
                AND CREA_USER_SQ IN (5,3) ) AS NEW_EOC_CNTRT_SQ
    FROM FAMSUNIT.EOCT020_CNTRT A       
    WHERE  A.ALIAS_CNTRT_ID IN ('12826')  
    AND CREA_USER_SQ NOT IN (5,3)
) S ON O.EOC_CNTRT_SQ = S.OLD_EOC_CNTRT_SQ

but when i am trying to convert it into an update statement i am getting error -

UPDATE O
SET O.EOC_CNTRT_SQ = S.NEW_EOC_CNTRT_SQ
FROM  FAMSUNIT.EOCT025_OPP_WRK O INNER JOIN
(
    SELECT A.EOC_CNTRT_SQ AS OLD_EOC_CNTRT_SQ, (SELECT B.EOC_CNTRT_SQ  
                FROM FAMSUNIT.EOCT020_CNTRT B 
                WHERE  B.ALIAS_CNTRT_ID = A.ALIAS_CNTRT_ID 
                AND CREA_USER_SQ IN (5,3) ) AS NEW_EOC_CNTRT_SQ
    FROM FAMSUNIT.EOCT020_CNTRT A       
    WHERE  A.ALIAS_CNTRT_ID IN ('12826')  
    AND CREA_USER_SQ NOT IN (5,3)
) S ON O.EOC_CNTRT_SQ = S.OLD_EOC_CNTRT_SQ;

ERROR [42601] [IBM][DB2] SQL0199N The use of the reserved word "FROM" following "" is not valid. Expected tokens may include: "( . MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE". SQLSTATE=42601

caleb.breckon
  • 1,336
  • 18
  • 42
joshig
  • 3
  • 1
  • 7
  • You need to brush up on the UPDATE statement syntax. `UPDATE SET = _________ WHERE .....` – caleb.breckon Jul 25 '14 at 21:32
  • i know the standard update statement but i am trying to update multiple records based on join with inner sub query for which i have formed the SQL statement. But not sure how to convert it into Update statement. Whatever i got is looking at the other examples from stack overflow – joshig Jul 25 '14 at 21:36
  • Possible duplicate of http://stackoverflow.com/questions/23285136/sql-update-from-one-table-to-another-based-on-a-id-match-in-db2 – mustaccio Jul 26 '14 at 00:31

1 Answers1

0
UPDATE
(
  SELECT O.EOC_CNTRT_SQ AS old_sq
       , S.NEW_EOC_CNTRT_SQ AS new_sq
    FROM  FAMSUNIT.EOCT025_OPP_WRK O
    INNER JOIN
    (
      SELECT A.EOC_CNTRT_SQ AS OLD_EOC_CNTRT_SQ
           , (
               SELECT B.EOC_CNTRT_SQ  
                 FROM FAMSUNIT.EOCT020_CNTRT B 
                 WHERE  B.ALIAS_CNTRT_ID = A.ALIAS_CNTRT_ID 
                 AND CREA_USER_SQ IN (5,3)
             ) AS NEW_EOC_CNTRT_SQ
        FROM FAMSUNIT.EOCT020_CNTRT A       
        WHERE  A.ALIAS_CNTRT_ID IN ('12826')  
        AND CREA_USER_SQ NOT IN (5,3)
    ) S ON O.EOC_CNTRT_SQ = S.OLD_EOC_CNTRT_SQ
) foo
SET foo.old_sq = foo.new_sq
;
caleb.breckon
  • 1,336
  • 18
  • 42
  • ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: " STATISTICS". SQLSTATE=42601 – joshig Jul 25 '14 at 21:58
  • A.) You seriously must know that that error tells me nothing. I can't let myself believe you don't know that. B.) If your `SELECT` statement actually works, then there's no reason this shouldn't work. – caleb.breckon Jul 25 '14 at 22:12
  • Sorry about that!! that sucks same way to me since i am using a homegrown db2 navigator and that's what all it says. Nevertheless i am just researching and got some one saying saying db2 doesn't DB2 does not accept join in update query - http://stackoverflow.com/questions/8008292/db2-update-with-join-queries. So looks like it needs to be converted into MERGE. I am working on that now... :( – joshig Jul 25 '14 at 22:17