Please help me on build sql for the requirement.

PRAVEEN T
PRAVEEN T used Ask the Experts™
on
Hi,

How to update the table column which are matched with reference table columns.

I have four rows in main table. on 2 rows are matching and updated the specified columns. I need to update other two columns which are not matched with the reference table.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Please find the  attached.
test_ref.xlsx
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I realize that Excel spreadsheet makes sense to you but it really doesn't help me.  From your previous question on this, I still don't see expected results.  In your other question I also saw where you wanted some column to have "not matched".  I don't see that here.

The solutions here will pretty much be the same in the other question:  I'll likely recommend MERGE again.

I also mentioned this in your other question:
We really need a very simple test case with a few columns, a few rows and the desired results.

There also appears to be problems with the Excel file you uploaded.  The MySQL for Excel is generating errors about a password vault when I try to open it.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
(blank) isn't a data condition in Oracle. So I have assume it was meant to be NULL.

BEFORE you run an update, please run and study the select query result:
select *
from maintbl m
inner join reftbl r on 
   m.TRANSACTIONTYPE = r.TRANSACTIONTYPE
and (m.REF = r.REF or (m.REF is null and r.REF is null)) 
and m.SOURCE = r.SOURCE
and m.SYSTEM = r.SYSTEM
and translate(m.JOURNAL,'^0123456789', '^') = translate(r.JOURNAL,'^X', '^')
;

Open in new window

When I ran it  got this:
| TRANSACTIONTYPE |  REF   | SOURCE | SYSTEM | JOURNAL  | DESCRIPTION | TRANSACTIONTYPE |  REF   | SOURCE | SYSTEM | JOURNAL  | DESCRIPTION |
|-----------------|--------|--------|--------|----------|-------------|-----------------|--------|--------|--------|----------|-------------|
| ABCD            | INV    | INV    | GIN    | INV1245  | deccription | ABCD            | INV    | INV    | GIN    | INVXXXX  | TEST ABCD   |
| ABCD            | INV    | INV    | GIN    | INV6564  | (null)      | ABCD            | INV    | INV    | GIN    | INVXXXX  | TEST ABCD   |
| ABCD            | INV    | INV    | GIN    | INV4455  | (null)      | ABCD            | INV    | INV    | GIN    | INVXXXX  | TEST ABCD   |
| DEF             | (null) | AP     | GAP    | PQRS2233 | (null)      | DEF             | (null) | AP     | GAP    | PQRSXXXX | TEST DEF    |
| DEF             | (null) | AP     | GAP    | PQRS7788 | (null)      | DEF             | (null) | AP     | GAP    | PQRSXXXX | TEST DEF    |
| DEF             | (null) | AP     | GAP    | PQRS9988 | (null)      | DEF             | (null) | AP     | GAP    | PQRSXXXX | TEST DEF    |
| DEF             | (null) | AP     | GAP    | PQRS5342 | (null)      | DEF             | (null) | AP     | GAP    | PQRSXXXX | TEST DEF    |
| GHI             | (null) | PO     | GPO    | PO3456   | (null)      | GHI             | (null) | PO     | GPO    | POXXXX   | TEST GHI    |
| GHI             | (null) | PO     | GPO    | PO7656   | (null)      | GHI             | (null) | PO     | GPO    | POXXXX   | TEST GHI    |
| GHI             | (null) | PO     | GPO    | PO8734   | (null)      | GHI             | (null) | PO     | GPO    | POXXXX   | TEST GHI    |
| GHI             | (null) | PO     | GPO    | PO1234   | (null)      | GHI             | (null) | PO     | GPO    | POXXXX   | TEST GHI    |

Open in new window

The update query I used was this:
TEST IT at http://sqlfiddle.com/#!4/04765/1

UPDATE MAINTBL m 
SET m.description = (SELECT r.description
                     FROM REFTBL r 
                     WHERE m.TRANSACTIONTYPE = r.TRANSACTIONTYPE
                      and (m.REF = r.REF or (m.REF is null and r.REF is null)) 
                      and m.SOURCE = r.SOURCE
                      and m.SYSTEM = r.SYSTEM
                      and translate(m.JOURNAL,'^0123456789', '^') = translate(r.JOURNAL,'^X', '^')
                    )
;

Open in new window

test details
[SQL Fiddle][1]

**Oracle 11g R2 Schema Setup**:

    CREATE TABLE REFTBL
        ("TRANSACTIONTYPE" varchar2(4), "REF" varchar2(7), "SOURCE" varchar2(3), "SYSTEM" varchar2(3), "JOURNAL" varchar2(8), "DESCRIPTION" varchar2(80))
    ;
    
    INSERT ALL 
        INTO REFTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INVXXXX', 'TEST ABCD')
        INTO REFTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('DEF', '', 'AP', 'GAP', 'PQRSXXXX', 'TEST DEF')
        INTO REFTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('GHI', '', 'PO', 'GPO', 'POXXXX', 'TEST GHI')
    SELECT * FROM dual
    ;
    
    CREATE TABLE MAINTBL
        ("TRANSACTIONTYPE" varchar2(4), "REF" varchar2(4), "SOURCE" varchar2(3), "SYSTEM" varchar2(3), "JOURNAL" varchar2(8), "DESCRIPTION" varchar2(80))
    ;
    
    INSERT ALL 
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INV1245', 'deccription')
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INV6564', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INV4455', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('DEF', '', 'AP', 'GAP', 'PQRS2233', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('DEF', '', 'AP', 'GAP', 'PQRS7788', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('DEF', '', 'AP', 'GAP', 'PQRS9988', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('DEF', '', 'AP', 'GAP', 'PQRS5342', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('GHI', '', 'PO', 'GPO', 'PO3456', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('GHI', '', 'PO', 'GPO', 'PO7656', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('GHI', '', 'PO', 'GPO', 'PO8734', NULL)
        INTO MAINTBL ("TRANSACTIONTYPE", "REF", "SOURCE", "SYSTEM", "JOURNAL", "DESCRIPTION")
             VALUES ('GHI', '', 'PO', 'GPO', 'PO1234', NULL)
    SELECT * FROM dual
    ;
**Query 1**:

    select *
    from maintbl m
    inner join reftbl r on 
       m.TRANSACTIONTYPE = r.TRANSACTIONTYPE
    and (m.REF = r.REF or (m.REF is null and r.REF is null)) 
    and m.SOURCE = r.SOURCE
    and m.SYSTEM = r.SYSTEM
    and translate(m.JOURNAL,'^0123456789', '^') = translate(r.JOURNAL,'^X', '^')
    

**[Results][2]**:
    | TRANSACTIONTYPE |    REF | SOURCE | SYSTEM |  JOURNAL | DESCRIPTION | TRANSACTIONTYPE |    REF | SOURCE | SYSTEM |  JOURNAL | DESCRIPTION |
    |-----------------|--------|--------|--------|----------|-------------|-----------------|--------|--------|--------|----------|-------------|
    |            ABCD |    INV |    INV |    GIN |  INV1245 | deccription |            ABCD |    INV |    INV |    GIN |  INVXXXX |   TEST ABCD |
    |            ABCD |    INV |    INV |    GIN |  INV6564 |      (null) |            ABCD |    INV |    INV |    GIN |  INVXXXX |   TEST ABCD |
    |            ABCD |    INV |    INV |    GIN |  INV4455 |      (null) |            ABCD |    INV |    INV |    GIN |  INVXXXX |   TEST ABCD |
    |             DEF | (null) |     AP |    GAP | PQRS2233 |      (null) |             DEF | (null) |     AP |    GAP | PQRSXXXX |    TEST DEF |
    |             DEF | (null) |     AP |    GAP | PQRS7788 |      (null) |             DEF | (null) |     AP |    GAP | PQRSXXXX |    TEST DEF |
    |             DEF | (null) |     AP |    GAP | PQRS9988 |      (null) |             DEF | (null) |     AP |    GAP | PQRSXXXX |    TEST DEF |
    |             DEF | (null) |     AP |    GAP | PQRS5342 |      (null) |             DEF | (null) |     AP |    GAP | PQRSXXXX |    TEST DEF |
    |             GHI | (null) |     PO |    GPO |   PO3456 |      (null) |             GHI | (null) |     PO |    GPO |   POXXXX |    TEST GHI |
    |             GHI | (null) |     PO |    GPO |   PO7656 |      (null) |             GHI | (null) |     PO |    GPO |   POXXXX |    TEST GHI |
    |             GHI | (null) |     PO |    GPO |   PO8734 |      (null) |             GHI | (null) |     PO |    GPO |   POXXXX |    TEST GHI |
    |             GHI | (null) |     PO |    GPO |   PO1234 |      (null) |             GHI | (null) |     PO |    GPO |   POXXXX |    TEST GHI |
**Query 2**:

    
    
    UPDATE MAINTBL m 
    SET m.description = (SELECT r.description
                         FROM REFTBL r 
                         WHERE m.TRANSACTIONTYPE = r.TRANSACTIONTYPE
                          and (m.REF = r.REF or (m.REF is null and r.REF is null)) 
                          and m.SOURCE = r.SOURCE
                          and m.SYSTEM = r.SYSTEM
                          and translate(m.JOURNAL,'^0123456789', '^') = translate(r.JOURNAL,'^X', '^')
                        )
    

**[Results][3]**:
**Query 3**:

    
    
    select
    *
    from maintbl
    

**[Results][4]**:
    | TRANSACTIONTYPE |    REF | SOURCE | SYSTEM |  JOURNAL | DESCRIPTION |
    |-----------------|--------|--------|--------|----------|-------------|
    |            ABCD |    INV |    INV |    GIN |  INV1245 |   TEST ABCD |
    |            ABCD |    INV |    INV |    GIN |  INV6564 |   TEST ABCD |
    |            ABCD |    INV |    INV |    GIN |  INV4455 |   TEST ABCD |
    |             DEF | (null) |     AP |    GAP | PQRS2233 |    TEST DEF |
    |             DEF | (null) |     AP |    GAP | PQRS7788 |    TEST DEF |
    |             DEF | (null) |     AP |    GAP | PQRS9988 |    TEST DEF |
    |             DEF | (null) |     AP |    GAP | PQRS5342 |    TEST DEF |
    |             GHI | (null) |     PO |    GPO |   PO3456 |    TEST GHI |
    |             GHI | (null) |     PO |    GPO |   PO7656 |    TEST GHI |
    |             GHI | (null) |     PO |    GPO |   PO8734 |    TEST GHI |
    |             GHI | (null) |     PO |    GPO |   PO1234 |    TEST GHI |

  [1]: http://sqlfiddle.com/#!4/04765/1
  [2]: http://sqlfiddle.com/#!4/04765/1/0
  [3]: http://sqlfiddle.com/#!4/04765/1/1
  [4]: http://sqlfiddle.com/#!4/04765/1/2

Open in new window

Author

Commented:
Hi PortletPaul,

Thank you for provided the solution.
I have tested with the queries , its working as expected.
As your query is updating the description. I need transaction type also in the main table.
And one more requirement any row that is not matched with the reference table , need to update the transaction type and description as ' Not Matched'

Please help me to complete this.

Thanks.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please use the below tested solution for you.

Data Generation

CREATE TABLE REF_TABLE
(
	  "TRANSACTION_TYPE" varchar2(50)
	, "REF" varchar2(7)
	, "SOURCE" varchar2(3)
	, "SYSTEM" varchar2(3)
	, "JOURNAL" varchar2(8)
	, "DESCRIPTION" varchar2(80)
)
;

INSERT ALL 
    INTO REF_TABLE
         VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INVXXXX', 'TEST ABCD')
    INTO REF_TABLE
         VALUES ('DEF', '', 'AP', 'GAP', 'PQRSXXXX', 'TEST DEF')
    INTO REF_TABLE
         VALUES ('GHI', '', 'PO', 'GPO', 'POXXXX', 'TEST GHI')
SELECT * FROM dual;


CREATE TABLE SCAL0801
(
	  "TRANSACTION_TYPE" varchar2(50)
	, "REF" varchar2(4)
	, "SOURCE" varchar2(3)
	, "SYSTEM" varchar2(3)
	, "JOURNAL" varchar2(8)
	, "DESCRIPTION" varchar2(80)
)
;

INSERT ALL 
    INTO SCAL0801
         VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INV1245', 'deccription')
    INTO SCAL0801
         VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INV6564', NULL)
    INTO SCAL0801
         VALUES ('ABCD', 'INV', 'INV', 'GIN', 'INV4455', NULL)
    INTO SCAL0801
         VALUES ('DEF', '', 'AP', 'GAP', 'PQRS2233', NULL)
    INTO SCAL0801
         VALUES ('DEF', '', 'AP', 'GAP', 'PQRS7788', NULL)
    INTO SCAL0801
         VALUES ('DEF', '', 'AP', 'GAP', 'PQRS9988', NULL)
    INTO SCAL0801
         VALUES ('DEF', '', 'AP', 'GAP', 'PQRS5342', NULL)
    INTO SCAL0801
         VALUES ('GHI', '', 'PO', 'GPO', 'PO3456', NULL)
    INTO SCAL0801
         VALUES ('GHI', '', 'PO', 'GPO', 'PO7656', NULL)
    INTO SCAL0801
         VALUES ('GHI', '', 'PO', 'GPO', 'PO8734', NULL)
    INTO SCAL0801
         VALUES ('GHI', '', 'PO', 'GPO', 'PO1234', NULL)
    INTO SCAL0801 
         VALUES ('RPS', '', 'PK', 'GPO', 'KP1234', NULL)
SELECT * FROM dual
;

Open in new window


Data before update

| TRANSACTION_TYPE |    REF | SOURCE | SYSTEM |  JOURNAL | DESCRIPTION |
|------------------|--------|--------|--------|----------|-------------|
|             ABCD |    INV |    INV |    GIN |  INV1245 | deccription |
|             ABCD |    INV |    INV |    GIN |  INV6564 |      (null) |
|             ABCD |    INV |    INV |    GIN |  INV4455 |      (null) |
|              DEF | (null) |     AP |    GAP | PQRS2233 |      (null) |
|              DEF | (null) |     AP |    GAP | PQRS7788 |      (null) |
|              DEF | (null) |     AP |    GAP | PQRS9988 |      (null) |
|              DEF | (null) |     AP |    GAP | PQRS5342 |      (null) |
|              GHI | (null) |     PO |    GPO |   PO3456 |      (null) |
|              GHI | (null) |     PO |    GPO |   PO7656 |      (null) |
|              GHI | (null) |     PO |    GPO |   PO8734 |      (null) |
|              GHI | (null) |     PO |    GPO |   PO1234 |      (null) |
|              RPS | (null) |     PK |    GPO |   KP1234 |      (null) |

Open in new window


Solution

SELECT * FROM SCAL0801;

MERGE INTO SCAL0801 s
USING 
(
  SELECT TRANSACTION_TYPE,REF,SOURCE,SYSTEM,JOURNAL,DESCRIPTION
  FROM REF_TABLE 
) ta ON ( ( ta.ref = s.ref OR (ta.ref IS NULL AND s.ref IS NULL ) )
         AND ta.source = s.source 
         and ta.SYSTEM = s.SYSTEM and 
         translate(s.JOURNAL,'^0123456789', '^') = translate(ta.JOURNAL,'^X', '^')
     )
WHEN MATCHED THEN UPDATE 
    SET s.TRANSACTION_TYPE = ta.TRANSACTION_TYPE 
	   ,s.DESCRIPTION = ta.DESCRIPTION  
;

UPDATE SCAL0801
SET    TRANSACTION_TYPE = 'Not Matched' , DESCRIPTION = 'Not Matched'
WHERE  NOT EXISTS (
                       SELECT 1
                       FROM   REF_TABLE ta
                       WHERE 
                        ( ta.ref = SCAL0801.ref OR (ta.ref IS NULL AND SCAL0801.ref IS NULL ) )
						 AND ta.source = SCAL0801.source 
						 and ta.SYSTEM = SCAL0801.SYSTEM and translate(SCAL0801.JOURNAL,'^0123456789', '^') = translate(ta.JOURNAL,'^X', '^')
				  )
; 

SELECT * FROM SCAL0801;

Open in new window


Data after update -

| TRANSACTION_TYPE |    REF | SOURCE | SYSTEM |  JOURNAL | DESCRIPTION |
|------------------|--------|--------|--------|----------|-------------|
|             ABCD |    INV |    INV |    GIN |  INV1245 |   TEST ABCD |
|             ABCD |    INV |    INV |    GIN |  INV6564 |   TEST ABCD |
|             ABCD |    INV |    INV |    GIN |  INV4455 |   TEST ABCD |
|              DEF | (null) |     AP |    GAP | PQRS2233 |    TEST DEF |
|              DEF | (null) |     AP |    GAP | PQRS7788 |    TEST DEF |
|              DEF | (null) |     AP |    GAP | PQRS9988 |    TEST DEF |
|              DEF | (null) |     AP |    GAP | PQRS5342 |    TEST DEF |
|              GHI | (null) |     PO |    GPO |   PO3456 |    TEST GHI |
|              GHI | (null) |     PO |    GPO |   PO7656 |    TEST GHI |
|              GHI | (null) |     PO |    GPO |   PO8734 |    TEST GHI |
|              GHI | (null) |     PO |    GPO |   PO1234 |    TEST GHI |
|      Not Matched | (null) |     PK |    GPO |   KP1234 | Not Matched |

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial