Please help me on build sql for the requirement.

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.
PRAVEEN TAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PRAVEEN TAuthor Commented:
Please find the  attached.
test_ref.xlsx
0
slightwv (䄆 Netminder) 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.
0
PortletPaulfreelancerCommented:
(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

0
PRAVEEN TAuthor 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.
0
Pawan KumarDatabase ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.