0

I was using below query in sql server to update the table "TABLE" using the same table "TABLE". In sql server the below query is working fine.But in DB2 its getting failed.Not sure whether I need to make any change in this query to work in DB2.

The error I am getting in DB2 is

ExampleExceptionFormatter: exception message was: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704

This is my input Data and there you can see ENO 679 is repeating in both round 3 and round 4.

enter image description here

My expected output is given below. Here I am taking the ID and round value from round 4 and updating rownumber 3 with the ID value from rownumber 4.

enter image description here

My requirement is to find the ENO which is exist in both round 3 and round 4 and update the values accordingly.

UPDATE  TGT 
SET TGT.ROUND = SRC.ROUND, 
TGT.ID = SRC.ID
FROM TABLE TGT INNER  JOIN TABLE SRC
ON TGT.ROUND='3' and SRC.ROUND='4' and TGT.ENO = SRC.ENO

Could someone help here please. I tried something like this.But its not working

UPDATE TABLE
    SET ID = (SELECT t.ID
                FROM TABLE t, TABLE t2
                WHERE t.ENO = t2.ENO AND t.ROUND= ='4' AND t2.ROUND='3'

               ) ,
ROUND= (SELECT t.ROUND
                FROM TABLE t, TABLE t2
                WHERE t.ENO = t2.ENO AND t.ROUND= ='4' AND t2.ROUND='3')
where ROUND='3'
GIN
  • 111
  • 1
  • 10
  • Can you show us the error, input output, table structure ? – Amira Bedhiafi Aug 30 '19 at 09:06
  • share your exact table structure and error message from above query – DarkRob Aug 30 '19 at 09:09
  • what is the issue in your first query....so you wrote this second query – DarkRob Aug 30 '19 at 09:16
  • My first query is working fine in sql server. But the same query is not working in DB2 – GIN Aug 30 '19 at 09:18
  • If your Db2-server platform+version supports it, then I would use the MERGE statement. – mao Aug 30 '19 at 09:50
  • Possible duplicate of [SQL update from one Table to another based on a ID match IN db2](https://stackoverflow.com/questions/23285136/sql-update-from-one-table-to-another-based-on-a-id-match-in-db2) – mustaccio Aug 30 '19 at 10:55
  • [SQL0204N](https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.messages.sql.doc/com.ibm.db2.luw.messages.sql.doc-gentopic1.html#sql0204n) error code means, that you try to use an undefined object in your query. What’s the full Db2 message you get? – Mark Barinstein Aug 31 '19 at 07:12

2 Answers2

0

You may try this. I think the issue is you are not relating your inner subquery with outer main table

UPDATE TABLE TB
    SET TB.ID = (SELECT t.ID
                FROM TABLE t, TABLE t2
                WHERE TB.ENO=t.ENO     ----  added this
                 and t.ENO = t2.ENO AND t.ROUND= ='4' AND t2.ROUND='3'
               ) ,
TB.ROUND= (SELECT t.ROUND
                FROM TABLE t, TABLE t2
                WHERE TB.ENO=t.ENO    --- added this
                and t.ENO = t2.ENO AND t.ROUND= ='4' AND t2.ROUND='3')
where tb.ROUND='3'
DarkRob
  • 3,843
  • 1
  • 10
  • 27
  • Thanks. But still I am getting some error .ExampleExceptionFormatter: exception message was: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601 – GIN Aug 30 '19 at 09:58
  • see this link to get the details about your error and resolve it..https://www-01.ibm.com/support/docview.wss?uid=swg21965866 – DarkRob Aug 30 '19 at 10:09
0

Try this:

UPDATE MY_SAMPLE TGT
SET (ID, ROUND) = (SELECT ID, ROUND FROM MY_SAMPLE WHERE ENO = TGT.ENO AND ROUND = 4)
WHERE ROUND = 4 AND EXISTS (SELECT 1 FROM MY_SAMPLE WHERE ENO = TGT.ENO AND ROUND = 4);

The difference with yours is that the correlated subquery has to be a row-subselect, it has to guarantee zero or one row (and will assign nulls in case of returning zero rows). The EXISTS subquery excludes rows for which the correlated subquery will not return rows.

Daniel Lema
  • 324
  • 2
  • 9