Update query with conditional joins?

laknar · #1 (**permalink**) 10-25-11, 22:30

Code:

create table tablea(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);

insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
insert into tablea values(13,103,1,'2011-06-01','2011-06-18');

create table tableb(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);

insert into tableb values(10,100,0,'2011-06-01','2011-06-30');
insert into tableb values(15,101,0,'2011-06-01','2011-06-30');
insert into tableb values(16,102,1,'2011-07-01','2011-07-20');

i need to update the table TABLEA based on the TABLEB

1.if PRODUCTID and RECORD_IND matches with TABLEB then update the table TABLEA only with PRODUCT_PID which is from TABLEB
2.if PRODUCTID matches and RECORD_IND not matches then update both the PRODUCT_PID and RECORD_IND fields from TABLEB.
3. if PRODUCTID not matches with TABLEB then delete the record from TABLEA.

thanks,
laknar

Peter.Vanroose · #2 (**permalink**) 10-26-11, 02:51

A single query for all 3 is of course impossible since UPDATE and DELETE cannot go in the same query. So you either need two statements (possibly with IF..ELSE logic, e.g. using SQL PL) or create an UPDATE trigger on TableA which does the delete, e.g. when RECORD_IND of TABLEA is set to NULL.

B.t.w.: cases 1 and 2 can easily be combined into the single
12: if PRODUCTID matches then update in table TABLEA both the PRODUCT_PID and RECORD_IND fields from TABLEB.

If you use the trigger solution, the single statement for all three becomes:

Code:

UPDATE tableA a
SET product_pid=(SELECT product_pid FROM tableB WHERE productid=a.productid),
    record_ind=(SELECT record_ind FROM tableB WHERE productid = a.productid)

tonkuma · #3 (**permalink**) 10-26-11, 09:15

Here are two examples.
These statements may not work on other DB2 version/release and platform OS.
Must Read before posting

I tested on DB2 Express-C 9.7 fixpack 4 on Windows/XP.

Code:

------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

   Database Connection Information

 Database server        = DB2/NT 9.7.4
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE


A JDBC connection to the target has succeeded.

Note: I used case 12 by Peter instead of case 1 and case 2, in Example 1 and Example 2.

Clean up tablea:

Code:

------------------------------ Commands Entered ------------------------------
DELETE FROM tablea;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
insert into tablea values(13,103,1,'2011-06-01','2011-06-18');
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

Example 1: common-table-expressions with data-change-table-reference

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        1          2011-06-01 2011-06-30
         11 101        1          2011-06-01 2011-06-30
         12 102        1          2011-07-01 2011-07-20
         13 103        1          2011-06-01 2011-06-18

  4 record(s) selected.


------------------------------ Commands Entered ------------------------------
WITH
 update_a AS (
SELECT COUNT(*) AS update_count
 FROM  FINAL TABLE
       (UPDATE tablea a
           SET (product_pid , record_ind)
             = (SELECT b.product_pid , b.record_ind
                 FROM  tableb b
                 WHERE b.productid = a.productid
               )
         WHERE EXISTS
               (SELECT 0 /* b.product_pid , b.record_ind */
                 FROM  tableb b
                 WHERE b.productid = a.productid
               )
       ) updt_a
)
, delete_a AS (
SELECT COUNT(*) AS delete_count
 FROM  OLD TABLE
       (DELETE FROM
               tablea a
         WHERE NOT EXISTS
               (SELECT 0 /* b.product_pid , b.record_ind */
                 FROM  tableb b
                 WHERE b.productid = a.productid
               )
       ) delete_a
)
SELECT update_count , delete_count
 FROM  update_a
     , delete_a
;
------------------------------------------------------------------------------

UPDATE_COUNT DELETE_COUNT
------------ ------------
           3            1

  1 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        0          2011-06-01 2011-06-30
         15 101        0          2011-06-01 2011-06-30
         16 102        1          2011-07-01 2011-07-20

  3 record(s) selected.

Clean up tablea again.

Example 2: MERGE with sysibm.sysdummy1

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        1          2011-06-01 2011-06-30
         11 101        1          2011-06-01 2011-06-30
         12 102        1          2011-07-01 2011-07-20
         13 103        1          2011-06-01 2011-06-18

  4 record(s) selected.


------------------------------ Commands Entered ------------------------------
MERGE INTO
      tablea a
USING sysibm.sysdummy1 s
  ON  s.ibmreqd = 'Y'
WHEN MATCHED
 AND EXISTS
     (SELECT 0
       FROM  tableb b
       WHERE b.productid = a.productid
     ) THEN
UPDATE
 SET (product_pid , record_ind)
   = (SELECT b.product_pid , b.record_ind
       FROM  tableb b
       WHERE b.productid = a.productid
     )       
WHEN MATCHED
 AND NOT EXISTS
     (SELECT 0
       FROM  tableb b
       WHERE b.productid = a.productid
     ) THEN
DELETE
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        0          2011-06-01 2011-06-30
         15 101        0          2011-06-01 2011-06-30
         16 102        1          2011-07-01 2011-07-20

  3 record(s) selected.

tonkuma · #4 (**permalink**) 10-26-11, 10:07

Example 3: Another MERGE statement.

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        1          2011-06-01 2011-06-30
         11 101        1          2011-06-01 2011-06-30
         12 102        1          2011-07-01 2011-07-20
         13 103        1          2011-06-01 2011-06-18

  4 record(s) selected.


------------------------------ Commands Entered ------------------------------
MERGE INTO
      tablea a
USING (SELECT productid
            , product_pid
            , record_ind
            , 'B'
        FROM  tableb
       UNION ALL
       SELECT productid
            , NULLIF(0 , 0)
            , NULLIF(0 , 0)
            , 'A'
        FROM  (SELECT productid
                FROM  tablea
               EXCEPT
               SELECT productid
                FROM  tableb
              ) a
      ) b(productid , product_pid , record_ind , table_id)
  ON  b.productid = a.productid
WHEN MATCHED
 AND table_id = 'B' THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
 AND table_id = 'A' THEN
DELETE
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        0          2011-06-01 2011-06-30
         15 101        0          2011-06-01 2011-06-30
         16 102        1          2011-07-01 2011-07-20

  3 record(s) selected.

Note: The following subquery may be rewritten by using NOT EXISTS or NOT IN predicate.

Code:

...
       UNION ALL
       SELECT productid
            , NULLIF(0 , 0)
            , NULLIF(0 , 0)
            , 'A'
        FROM  (SELECT productid
                FROM  tablea
               EXCEPT
               SELECT productid
                FROM  tableb
              ) a
      ) b(productid , product_pid , record_ind , table_id)
...

laknar · #5 (**permalink**) 10-26-11, 21:09

Tonkuma, Thank you so much

one more scenario there is a possibility of one to many when joining. in that case we need to pick MIN(Record_IND).

Code:

create table tablea(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);

insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
insert into tablea values(13,103,1,'2011-06-01','2011-06-18');

create table tableb(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);

insert into tableb values(10,100,0,'2011-06-01','2011-06-30');
insert into tableb values(15,101,0,'2011-06-01','2011-06-30');
insert into tableb values(16,102,1,'2011-07-01','2011-07-20');
insert into tableb values(17,102,0,'2011-07-01','2011-07-20');
insert into tableb values(18,101,0,'2011-06-01','2011-06-30');

tonkuma · #6 (**permalink**) 10-26-11, 22:34

Quote:

... there is a possibility of one to many when joining. in that case we need to pick MIN(Record_IND).

How about product_pid?

I guessed that you want to pick a row with MIN(Record_IND).
In the sample data, take this row for productid = 102
(17,102,0,'2011-07-01','2011-07-20')

An example: (Not tested)
Instead of:

Code:

        FROM  tableb

use:
Correction: Remove keyword "DESC"

Code:

        FROM  (SELECT b.*
                    , ROW_NUMBER()
                         OVER(PARTITION BY productid
                                  ORDER BY record_ind) AS row_num
                FROM  tableb b
              ) b
        WHERE row_num = 1

tonkuma · #7 (**permalink**) 10-26-11, 23:00

Must Read before posting

Quote:

...

1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.
You can get his info using the following commands
db2level -> to get db2 version and fixpack level
db2licm -l -> to get the db2 type (WSE, ESE, etc)

...

If you are using DB2 9.7 fixpack 4 for LUW,
you can use
1) LISTAGG aggregate function
2) column name(s) in a pattern expression of a LIKE predicate.

Example 4:(no one to many when joining)

Code:

MERGE INTO
      tablea a
USING (SELECT b.*
            , LISTAGG( CHAR(productid) , ',' )
                 OVER() AS b_productid_all
            , ROW_NUMBER()
                 OVER() AS row_num
        FROM  tableb b
      ) b
  ON  b.productid = a.productid
  OR  b_productid_all
      NOT LIKE '%' || CHAR(a.productid) || '%'
  AND row_num = 1
WHEN MATCHED
 AND b.productid =  a.productid THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
 AND b.productid <> a.productid THEN
DELETE
;

Example 5:(no one to many when joining)

Code:

MERGE INTO
      tablea a
USING (SELECT b.*
            , LISTAGG( CHAR(productid) , ',' )
                 OVER() AS b_productid_all
        FROM  tableb b
      ) b
  ON  b.productid = a.productid
  OR  b_productid_all
      NOT LIKE '%' || CHAR(a.productid) || '%'
  AND b_productid_all
          LIKE CHAR(b.productid) || '%'
WHEN MATCHED
 AND b.productid =  a.productid THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
 AND b.productid <> a.productid THEN
DELETE
;

Note: You can remove the condition "AND b.productid <> a.productid" in Example 4 and Example 5,
because...

Quote:

MERGE statement

...

Description

...
...

WHEN matching-condition
Specifies the condition under which the modification-operation or the signal-statement is executed. Each matching-condition is evaluated in order of specification. Rows for which the matching-condition evaluates to true are not considered in subsequent matching conditions.

...

See details in
MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows

laknar · #8 (**permalink**) 10-26-11, 23:27

Thank you for your quick reply.

Code:

create table tablea(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);

insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
insert into tablea values(13,103,1,'2011-06-01','2011-06-18');

create table tableb(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);

insert into tableb values(10,100,0,'2011-06-01','2011-06-30');
insert into tableb values(15,101,0,'2011-06-01','2011-06-30');
insert into tableb values(16,102,1,'2011-07-01','2011-07-20');
insert into tableb values(17,102,0,'2011-07-01','2011-07-20');
insert into tableb values(18,101,2,'2011-06-01','2011-06-30');

1. we have to use both Productid and Record_ind if a match found then update the corresponding row to Tablea.(column Product_pid)

2. if only Productid matches and record_ind not matches then update the Product_pid and min(record_ind) from tableb to tablea

3. if productid does not match then delete the corresponding record in tablea.

i have a query written to update this. but im stuck with second logic.

prtoductid 102 for first scenario.
productid 101 for second scenario.
productid 100 also for second scenario.
productid 103 for third scenario.

Please suggest me.

tonkuma · #9 (**permalink**) 10-27-11, 01:53

I couldn't understand intention of your last post.
I thought that your last post was only repeated original post and your second to last post.

Anyhow, here is an example.
If the result was different from your required result,
please show your required result by the format of result of "SELECT * FROM tablea".

Example 4-1:

Code:

------------------------------ Commands Entered ------------------------------
MERGE INTO
      tablea a
USING (SELECT b.*
            , LISTAGG( CHAR(productid) , ',' )
                 OVER() AS b_productid_list
            , ROW_NUMBER()
                 OVER() AS row_num
            , ROW_NUMBER()
                 OVER( PARTITION BY productid
                           ORDER BY record_ind
                     )  AS rn_record_ind
        FROM  tableb b
      ) b
  ON  b.productid   = a.productid
  AND rn_record_ind = 1
  OR  b_productid_list
      NOT LIKE '%' || CHAR(a.productid) || '%'
  AND row_num = 1
WHEN MATCHED
 AND b.productid =  a.productid
THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
THEN
DELETE
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        0          2011-06-01 2011-06-30
         15 101        0          2011-06-01 2011-06-30
         17 102        0          2011-07-01 2011-07-20

  3 record(s) selected.

tonkuma · #10 (**permalink**) 10-27-11, 08:45

Another example using LISTAGG function.

Example 6:

Code:

------------------------------ Commands Entered ------------------------------
MERGE INTO
      tablea a
USING (SELECT product_pid , productid , record_ind
            , LISTAGG( CHAR(productid) , ',' )
                 OVER() AS b_productid_list
            , ROW_NUMBER()
                 OVER( PARTITION BY productid
                           ORDER BY record_ind
                     )  AS rn_record_ind
        FROM  tableb b
        GROUP BY
              ROLLUP( (product_pid , productid , record_ind) ) 
      ) b
  ON  b.productid = a.productid
  AND rn_record_ind = 1
  OR  b_productid_list
      NOT LIKE '%' || CHAR(a.productid) || '%'
  AND b.productid IS NULL
WHEN MATCHED
 AND b.productid =  a.productid
THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
THEN
DELETE
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        0          2011-06-01 2011-06-30
         15 101        0          2011-06-01 2011-06-30
         17 102        0          2011-07-01 2011-07-20

  3 record(s) selected.

tonkuma · #11 (**permalink**) 10-30-11, 22:56

I tried to reduce number of predicates in ON clause.

Example 7: Two pewdicates in ON clause.
Note:
p1: COALESCE(...) = a.productid
p2: rn_record_ind = 1

Code:

MERGE INTO
      tablea a
USING (SELECT product_pid , productid , record_ind
            , LISTAGG( productid , ',' )
                 OVER() AS b_productid_list
            , ROW_NUMBER()
                 OVER( PARTITION BY productid
                           ORDER BY record_ind
                     )  AS rn_record_ind
        FROM  tableb b
        GROUP BY
              ROLLUP( (product_pid , productid , record_ind) ) 
      ) b
  ON  COALESCE( b.productid
              , CASE LOCATE(a.productid , b_productid_list)
                WHEN 0 THEN a.productid
                ELSE        ''
                END
              ) = a.productid
  AND rn_record_ind = 1
WHEN MATCHED
 AND b.productid =  a.productid
THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
THEN
DELETE
;

Peter.Vanroose · #12 (**permalink**) 10-31-11, 04:09

Shouldn't the second "WHEN MATCHED" be "WHEN NOT MATCHED" ?

tonkuma · #13 (**permalink**) 10-31-11, 05:37

The rows in tablea which were not matched with tableb would be matched with grand-total row of subquery "b".

For example,
a row in tablea with productid = 103 doesn't match with tableb,
but it was matched with grand-total(productid is null) row of subquery "b".

Sample Data:

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        1          2011-06-01 2011-06-30
         11 101        1          2011-06-01 2011-06-30
         12 102        1          2011-07-01 2011-07-20
         13 103        1          2011-06-01 2011-06-18

  4 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM tableb;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        0          2011-06-01 2011-06-30
         15 101        0          2011-06-01 2011-06-30
         16 102        1          2011-07-01 2011-07-20
         17 102        0          2011-07-01 2011-07-20
         18 101        2          2011-06-01 2011-06-30

  5 record(s) selected.

Modified query to show the result of matching:

Code:

------------------------------ Commands Entered ------------------------------
--MERGE INTO
SELECT a.product_pid , a.productid , a.record_ind
     , b.product_pid , b.productid , b.record_ind
     , VARCHAR(b_productid_list , 30) AS b_productid_list
 FROM
      tablea a
--USING 
 LEFT OUTER JOIN
      (SELECT product_pid , productid , record_ind
            , LISTAGG( productid , ',' )
                 OVER() AS b_productid_list
            , ROW_NUMBER()
                 OVER( PARTITION BY productid
                           ORDER BY record_ind
                     )  AS rn_record_ind
        FROM  tableb b
        GROUP BY
              ROLLUP( (product_pid , productid , record_ind) ) 
      ) b
  ON  COALESCE( b.productid
              , CASE LOCATE(a.productid , b_productid_list)
                WHEN 0 THEN a.productid
                ELSE        ''
                END
              ) = a.productid
  AND rn_record_ind = 1
/*
WHEN MATCHED
 AND b.productid =  a.productid
THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
THEN
DELETE
*/
;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND PRODUCT_PID PRODUCTID  RECORD_IND B_PRODUCTID_LIST              
----------- ---------- ---------- ----------- ---------- ---------- ------------------------------
         10 100        1                   10 100        0          100,101,101,102,102           
         11 101        1                   15 101        0          100,101,101,102,102           
         12 102        1                   17 102        0          100,101,101,102,102           
         13 103        1                    - -          -          100,101,101,102,102           

  4 record(s) selected.

Peter.Vanroose · #14 (**permalink**) 10-31-11, 05:42

OK, I see.
Didn't know that MERGE accepts more than one "WHEN MATCHED" clause!

tonkuma · #15 (**permalink**) 10-31-11, 05:56

Yes, MERGE accepts more than one "WHEN MATCHED" clause.

Here is the result of MERGE statement for data in my previous post(same data supplied by laknar).

Code:

------------------------------ Commands Entered ------------------------------
MERGE INTO
/*
SELECT a.product_pid , a.productid , a.record_ind
     , b.product_pid , b.productid , b.record_ind
     , VARCHAR(b_productid_list , 30) AS b_productid_list
 FROM
*/
      tablea a
USING 
-- LEFT OUTER JOIN
      (SELECT product_pid , productid , record_ind
            , LISTAGG( productid , ',' )
                 OVER() AS b_productid_list
            , ROW_NUMBER()
                 OVER( PARTITION BY productid
                           ORDER BY record_ind
                     )  AS rn_record_ind
        FROM  tableb b
        GROUP BY
              ROLLUP( (product_pid , productid , record_ind) ) 
      ) b
  ON  COALESCE( b.productid
              , CASE LOCATE(a.productid , b_productid_list)
                WHEN 0 THEN a.productid
                ELSE        ''
                END
              ) = a.productid
  AND rn_record_ind = 1

WHEN MATCHED
 AND b.productid =  a.productid
THEN
UPDATE
   SET (  product_pid ,   record_ind)
     = (b.product_pid , b.record_ind)
WHEN MATCHED
THEN
DELETE
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM tablea;
------------------------------------------------------------------------------

PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
----------- ---------- ---------- ---------- ----------
         10 100        0          2011-06-01 2011-06-30
         15 101        0          2011-06-01 2011-06-30
         17 102        0          2011-07-01 2011-07-20

  3 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech