CODEWITHSUNDEEP
sqldb2
Nonas Buzines
Nonas Buzines

Reputation: 25

3 table compare for UPDATE

DB2 UDB 11.x

I'm not sure why this is so difficult but I have not been able to achieve what I want to after about a few dozen variations of my update query. Found a bunch of posts but none seem to quite work. Here is my scenario: 

TABLE1
NAMEPK SIZE 
AAA    0 
BBB    100 
CCC    0 
DDD    50  

TABLE2 
NAMEPK COL2 COL3 COL4 
AAA    99   99   N 
BBB    99   99   N 
CCC    98   99   N  
DDD    100  100  Y  <--eliminated because COL4 is Y

TABLE 3 
NAMEPK COL2PK COL3PK COL9 SIZE 
AAA    99     99     5379 9999 <-- want this because name, col2, col3 match TABLE2 and COL9 set to 5379 and TABLE1.size <> TABLE3.size
AAA    98     99     5379 9988 <--eliminated because COL2 doesnt match TABLE2
AAA    97     99     1000 8989 <--eliminated because COL2 doesnt match TABLE2 and COL9 is not 5379
BBB    99     90     5379 100  <--eliminated because COL3 doesnt match TABLE2
BBB    99     99     5379 100  <--eliminated because TABLE1.size == TABLE3.size
BBB    99     99     5300 9999 <--eliminated because COL9 is not 5379
DDD    100    100    1000 7777 <--eliminated because DDD was eliminated previously since COL4 = Y

edit: corrected some of the notes above

columns with 'PK' indicate these are the primary keys for the table. TABLE1 and TABLE2 have a 1:1 relationship. TABLE3 can contain multiple rows with the same NAMEPK so has to be compared with TABLE2 to match the record. TABLE3 contains the value for SIZE that I need to update the same column in TABLE1.

Based on this info, the results should look like this: 

RESULTS of TABLE 1
NAMEPK SIZE 
AAA    9999 <--only column that was updated
BBB    100 
CCC    0 
DDD    50

I was easily able to create a SELECT statement to get the correct output: 

db2 "SELECT T1.namepk, T1.size, T3.size 
      FROM table1 T1, table1 T2, table3 T3 \
      WHERE T1.namepk = T2.namepk AND \
            T2.namepk = T3.namepk AND \
            T2.col2   = T3.col2   AND \
            T2.col3   = T3.col3   AND \
            T2.col4   = 'N'       AND \
            T3.col9   = 5379      AND \
            T1.size   <> T3.size)"

But when I try to convert this into an UPDATE statement I get various errors or incorrect results. Here is one of the many attempts: 

db2 "UPDATE table1 T1 SET T1.size = T3.size \
       WHERE namepk \
         IN (SELECT T1.namepk, T3.size 
               FROM table1 T1, table1 T2, table3 T3 \
               WHERE T1.namepk = T2.namepk AND \
                     T2.namepk = T3.namepk AND \
                     T2.col2   = T3.col2   AND \
                     T2.col3   = T3.col3   AND \
                     T2.col4   = 'N'       AND \
                     T3.col9   = 5379      AND \
                     T1.size   <> T3.size)"

With this I get the following error: 

SQL0206N  "T3.SIZE" is not valid in the context where it is used.

so, it doesnt know what T3.SIZE is because it's outside of the SELECT query? So I tried other variations where I do something like: 

db2 "UPDATE table1 T1 \
      SET T1.size = (SELECT T2.size \
                       FROM table1 T1, table2 T2, table3 T3 \
                        WHERE T1.namepk = T2.namepk AND \
                              T2.namepk = T3.namepk AND \
                              T2.col2   = T3.col2   AND \
                              T2.col3   = T3.col3   AND \
                              T2.col4   = 'N'       AND \
                              T3.col9   = 5379      AND \
                              T1.size   <> T3.size)"

and get this error: 

SQL0811N  The result of a scalar fullselect, SELECT INTO statement, or VALUES
INTO statement is more than one row.

I pretty much get one of those two errors no matter what I try. Your help is appreciated

Upvotes: 0

Views: 42

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Try this:

MERGE INTO TABLE1 T1 USING
(
SELECT T3.NAMEPK, T3.SIZE 
FROM TABLE2 T2
JOIN TABLE3 T3 ON T3.NAMEPK = T2.NAMEPK AND T3.COL2PK = T2.COL2 AND T3.COL3PK = T2.COL3
WHERE T2.COL4 = 'N' AND T3.COL9 = 5379
) T23 ON T23.NAMEPK = T1.NAMEPK AND T23.SIZE <> T1.SIZE
WHEN MATCHED THEN UPDATE SET SIZE = T23.SIZE;

The statement fails, if T23 has multiple rows for the same NAMEPK with SIZE not equal to TABLE1.SIZE for this NAMEPK.

Upvotes: 1

Related Questions