Update using joins

pratikp.vasani · #1 (**permalink**) 07-08-11, 08:40

Please provide the syntax to update using joins.
I tried many syntax but errors were there.
I also tried MERGE but it also did not work.

sathyaram_s · #2 (**permalink**) 07-08-11, 08:54

Can you post what SQL you have used and what error you get?

pratikp.vasani · #3 (**permalink**) 07-08-11, 08:57

MERGE INTO mbid.TSCHCLD F
USING mbid.TSCH A
ON F.FLIGHT_NUMBER = A.FLIGHT_NUMBER AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
AND F.FLIGHT_DESIGNATOR = A.FLIGHT_DESIGNATOR AND F.FLIGHT_OP_SUFFIX = A.FLIGHT_OP_SUFFIX AND
F.FLIGHT_NUMBER = 24
AND F.FLIGHT_DESIGNATOR = 'DD'
WHEN MATCHED THEN UPDATE SET F.DEI_2 = 'AA';

The Error is-
SQL State = 21506 SQL Code = -788 SQL Message = The same row of target table "MBID.TMSTSCHCLD" was identified more than once for an update, delete or insert operation of the MERGE statement. Exception message = com.ibm.db2.jcc.c.SqlException: The same row of target table "MBID.TMSTSCHCLD" was identified more than once for an update, delete or insert operation of the MERGE statement.

sathyaram_s · #4 (**permalink**) 07-08-11, 09:33

Your join condition should be such that for each row selected to be updated in F , there should be only one row in A. As per this error, there is more than one row.

Either you have missed a filter condition or it could be a design flaw or it could be constraint error that has allowed duplicate rows to mbid.TSCH.

If none of these is true, do

Code:

MERGE INTO mbid.TSCHCLD F
USING (select on mbid.TSCH to return only distinct rows for each fltno,deldate,design,suffix) a 
ON F.FLIGHT_NUMBER = A.FLIGHT_NUMBER AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
AND F.FLIGHT_DESIGNATOR = A.FLIGHT_DESIGNATOR AND F.FLIGHT_OP_SUFFIX = A.FLIGHT_OP_SUFFIX AND 
F.FLIGHT_NUMBER = 24 
AND F.FLIGHT_DESIGNATOR = 'DD' 
WHEN MATCHED THEN UPDATE SET F.DEI_2 = 'AA';

You can find the offending row using

Code:

Select F.FLIGHT_NUMBER, F.FIRST_LEG_DEP_DATE, F.FLIGHT_DESIGNATOR,count(*) from mbid.TSCHCLD F, mbid.TSCH A 
Where  
F.FLIGHT_NUMBER = A.FLIGHT_NUMBER AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
AND F.FLIGHT_DESIGNATOR = A.FLIGHT_DESIGNATOR AND F.FLIGHT_OP_SUFFIX = A.FLIGHT_OP_SUFFIX AND 
F.FLIGHT_NUMBER = 24 
AND F.FLIGHT_DESIGNATOR = 'DD'

FYI, The above query was done on notepad and not checked for syntax or output.

Cheers
Sathyaram

pratikp.vasani · #5 (**permalink**) 07-11-11, 01:45

Instead of MERGE, can't we use simple JOINT query.
Also in the Where clause I will be having the conditions which will update multiple rows together.

tonkuma · #6 (**permalink**) 07-11-11, 02:23

You have not used column value(s) of A to update F.DEI_2.

So, try
Example (a):

Code:

UPDATE mbid.TSCHCLD F
   SET F.DEI_2 = 'AA'
 WHERE EXISTS
       (SELECT 0
         FROM  mbid.TSCH A
         WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
           AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
           AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
           AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
           AND F.FLIGHT_NUMBER      = 24 
           AND F.FLIGHT_DESIGNATOR  = 'DD'
       )

or
Example (b):

Code:

UPDATE mbid.TSCHCLD F
   SET F.DEI_2 = 'AA'
 WHERE EXISTS
       (SELECT 0
         FROM  mbid.TSCH A
         WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
           AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
           AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
           AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
       )
   AND F.FLIGHT_NUMBER      = 24 
   AND F.FLIGHT_DESIGNATOR  = 'DD'

You will get same result by (a) and (b).
And if DB2 optimizer was clever enough, it might choose same access path.

pratikp.vasani · #7 (**permalink**) 07-11-11, 02:54

Hi,
In the above query I am using one more condition that A.ACTION_IDEN <> 'CNL', but it is updating that row also. And the column ACTION_IDEN is not present in the second table.
So is it possible with the above situation.

tonkuma · #8 (**permalink**) 07-11-11, 05:31

Quote:

In the above query I am using one more condition that A.ACTION_IDEN <> 'CNL', but it is updating that row also.

I couldn't understand well your requirements and issue.
If you include the condition(A.ACTION_IDEN <> 'CNL'),
SQL wouldn't update the rows having corresponding row(s) with A.ACTION_IDEN = 'CNL'.
One possibility I could guess was there were multiple corresponding rows in A
but some were A.ACTION_IDEN = 'CNL' and others were A.ACTION_IDEN <> 'CNL'.

Quote:

And the column ACTION_IDEN is not present in the second table.

No problem.

If you want to exclude the rows which have corresponding row(s) in A but all A.ACTION_IDEN = 'CNL',
simply add the condition, like...

Code:

UPDATE mbid.TSCHCLD F
   SET F.DEI_2 = 'AA'
 WHERE EXISTS
       (SELECT 0
         FROM  mbid.TSCH A
         WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
           AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
           AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
           AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
           AND A.ACTION_IDEN        <> 'CNL'
       )
   AND F.FLIGHT_NUMBER      = 24 
   AND F.FLIGHT_DESIGNATOR  = 'DD'
;

tonkuma · #9 (**permalink**) 07-11-11, 20:45

If your requirements were

Quote:

Update F if there were matched rows in A,
but not update if some of matched row(s) were A.ACTION_IDEN = 'CNL'.
In other words:
Update F if there were matched rows in A and all of the matched rows were A.ACTION_IDEN <> 'CNL'.

try...

Code:

UPDATE mbid.TSCHCLD F
   SET F.DEI_2 = 'AA'
 WHERE EXISTS
       (SELECT 0
         FROM  mbid.TSCH A
         WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
           AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
           AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
           AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
         HAVING
               SUM(CASE A.ACTION_IDEN
                   WHEN 'CNL' THEN 1
                   ELSE            0
                   END
                  ) = 0
       )
   AND F.FLIGHT_NUMBER      = 24 
   AND F.FLIGHT_DESIGNATOR  = 'DD'
;

Note: If your DB2 supports LISTAGG aggregate function, HAVING clause can be replaced by
HAVING LOCATE( 'CNL:' , LISTAGG(a.action_iden || ':') ) = 0
or
HAVING LISTAGG(a.action_iden || ':') NOT LIKE '%CNL:%'

Internet Services

Web Development

Digital Marketing

Consumer Tech