update linked tables

ali şahin
ali şahin used Ask the Experts™
on
hi expert
Values ​​in column "FATURA_FATURA_NO" in table "FATURADETAIL"
I want to update the column "ISLEMYAP_FATURA_NO" in the "ISLEMYAP" table
Rowid1 columns common in two tables.
I connected these tables with each other rowid1 column.
I tried to explain what I wanted as a picture.
The information in the image is the result of the following query.
 SELECT IY.FATURA_NO AS ISLEMYAP_FATURA_NO, IY.ROWID1 as ISLEMYAP_rowid1 ,FT.ROWID1 as FATURADETAIL_rowid1,
 FT.FATURA_NO AS FATURA_FATURA_NO
  FROM FATURADETAIL FT,ISLEMYAP IY WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL 

Open in new window

The update I wrote is as follows
update ISLEMYAP IY
set 
   IY.FATURA_NO=( SELECT FT.FATURA_NO
                                     FROM FATURADETAIL FT
                                      WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL)
WHERE EXISTS (
    SELECT 1
      FROM FATURADETAIL FT
     WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL )

Open in new window

I think the update goes into an infinite loop
the process does not stop...
I did update by typing plsql.
but I could not update with this code.
how can i fix it.?
how should I fix this code
thanks
deneme_calismalari2.png
Comment
Watch Question

Do more with

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

Commented:
I'm not sure, that I understand your logic:

The correlated sub-query

SELECT *
FROM   FATURADETAIL FT
WHERE  IY.ROWID1 = FT.ROWID1
     AND IY.TARIH >= TO_DATE('01.01.2016', 'DD.MM.YYYY')
     AND IY.TARIH < TO_DATE('01.11.2016', 'DD.MM.YYYY')
     AND IY.FATURA_TIPI = 2
     AND IY.FATURA_NO IS NULL

Open in new window

is used twice. For making sense, is guaranteed, that it returns a single row?

In this case, the UPDATE should be:

UPDATE
(
    SELECT  IY.FATURA_NO AS FATURA_NO_OLD ,
            FT.FATURA_NO AS FATURA_NO_NEW
    FROM   ISLEMYAP IY
           INNER JOIN FATURADETAIL FT ON IY.ROWID1 = FT.ROWID1
    WHERE  IY.TARIH >= TO_DATE('01.01.2016', 'DD.MM.YYYY')
           AND IY.TARIH < TO_DATE('01.11.2016', 'DD.MM.YYYY')
           AND IY.FATURA_TIPI = 2
           AND IY.FATURA_NO IS NULL
) T
SET T.FATURA_NO_OLD = T.FATURA_NO_NEW;

Open in new window

And it's for sure not an endless loop, maybe just a lot of data involved. Thus you may create an (check for) index on ISLEMYAP ( FATURA_TIPI, FATURA_NO, TARIH, ROWID1) and on FATURADETAIL ( ROWID1,FATURA_NO).

Author

Commented:
In this case, the UPDATE should be:

UPDATE
(
    SELECT  IY.FATURA_NO AS FATURA_NO_OLD ,
            FT.FATURA_NO AS FATURA_NO_NEW
    FROM   ISLEMYAP IY
           INNER JOIN FATURADETAIL FT ON IY.ROWID1 = FT.ROWID1
    WHERE  IY.TARIH >= TO_DATE('01.01.2016', 'DD.MM.YYYY')
           AND IY.TARIH < TO_DATE('01.11.2016', 'DD.MM.YYYY')
           AND IY.FATURA_TIPI = 2
           AND IY.FATURA_NO IS NULL
) T
SET T.FATURA_NO_OLD = T.FATURA_NO_NEW;

this code give me eror
[Error] Execution (12: 5): ORA-01779: cannot modify a column which maps to a non key-preserved table

Open in new window

johnsoneSenior Oracle DBA

Commented:
Your update statement looks correct.  Definitely look at indexing.  Depending on the volume of data in the tables, creating a small intermediate table could help, might not though.

Changing it to updating with an inline view, which has a lot of restrictions in Oracle, is likely to generate a worse execution plan and take longer.  This is the SQL Server way to do things.

Generate a execution plan like this and post the result:
explain plan for
update ISLEMYAP IY
set 
   IY.FATURA_NO=( SELECT FT.FATURA_NO
                                     FROM FATURADETAIL FT
                                      WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL)
WHERE EXISTS (
    SELECT 1
      FROM FATURADETAIL FT
     WHERE IY.ROWID1=FT.ROWID1 AND 
                                                       IY.TARIH>=TO_DATE('01.01.2016','DD.MM.YYYY') AND IY.TARIH<TO_DATE('01.11.2016','DD.MM.YYYY') AND
                                                        IY.FATURA_TIPI=2  and
                                                        IY.FATURA_NO IS NULL );
select plan_table_output from table(dbms_xplan.display());

Open in new window


Rewriting the statement as a MERGE could be more efficient.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I would definitely suggest MERGE:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F

It is really efficient in how it does things.  There are many examples on the web.

If you would like a copy/paste solution, please provide sample tables, data and expected results.

Author

Commented:
select plan_table_output from table(dbms_xplan.display());

Open in new window



--------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |              |     1 |    65 |  8298M  (2)|
|   1 |  UPDATE              | ISLEMYAP     |       |       |            |
|   2 |   FILTER             |              |       |       |            |
|   3 |    TABLE ACCESS FULL | ISLEMYAP     |  6984K|   432M| 82837   (2)|
|   4 |    FILTER            |              |       |       |            |
|   5 |     TABLE ACCESS FULL| FATURADETAIL |     2 |    18 |  1189   (2)|
|   6 |   FILTER             |              |       |       |            |
|   7 |    TABLE ACCESS FULL | FATURADETAIL |     2 |    32 |  1189   (2)|
--------------------------------------------------------------------------
 
Note
-----
   - 'PLAN_TABLE' is old version
johnsoneSenior Oracle DBA

Commented:
Are your statistics up to date?  Those row counts look strange.  You may also have improper indexing for this particular query.

A MERGE would help as it should not hit the FATURADETAIL table more than once.
ste5anSenior Developer

Commented:
hmm, your plan and
[Error] Execution (12: 5): ORA-01779: cannot modify a column which maps to a non key-preserved table
tell me that you need to look in to your indices.

- Do both tables have a primary key?
- Is RowID1 indexed?

And test it with the indices on ISLEMYAP ( FATURA_TIPI, FATURA_NO, TARIH, ROWID1) and on FATURADETAIL ( ROWID1,FATURA_NO).
Mark GeerlingsDatabase Administrator

Commented:
This error:
ORA-01779: cannot modify a column which maps to a non key-preserved table
indicates that you are updating a view, not a table, and that your attempted update would make at least one of the records no longer visible in the view.

Can you change your update to update a table directly instead of updating the view?

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