?
Solved

oracle sql update join two tables

Posted on 2007-10-05
5
Medium Priority
?
17,987 Views
Last Modified: 2012-06-21
I hava the following update query:
 
UPDATE CUSTOMER_TRANSACTIONS CTC
SET CTC.PERIODID = '2005-2006', CTC.PROCESSID = '200612'
FROM CUSTOMER_TRANSACTIONS_20070716 CTO
WHERE CTO.PERIODID = '2005-2006'
AND CTO.PROCESSID = '200612'
AND CTO.COMPANYID = CTC.COMPANYID
AND CTO.CUSTOMERID = CTC.CUSTOMERID
AND CTO.APPOINTOFCAID = CTC.APPOINTOFCAID
AND CTO.ACCKINDID = CTC.ACCKINDID
AND SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) = SUBSTR(CTC.TRNDOCNO, 0, INSTR(CTC.TRNDOCNO, '_'))
AND CTO.PERIODID <> CTC.PERIODID
AND CTO.PROCESSID <> CTC.PROCESSID;

but not working any idea?
0
Comment
Question by:gla
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 20020816
please check out this syntax:
http://www.psoug.org/reference/update.html

UPDATE CUSTOMER_TRANSACTIONS CTC
SET ( PERIODID ,  PROCESSID  ) =
  (SELECT '2005-2006', '200612'
    FROM CUSTOMER_TRANSACTIONS_20070716 CTO
    WHERE CTO.PERIODID = '2005-2006'
    AND CTO.PROCESSID = '200612'
    AND CTO.COMPANYID = CTC.COMPANYID
    AND CTO.CUSTOMERID = CTC.CUSTOMERID
    AND CTO.APPOINTOFCAID = CTC.APPOINTOFCAID
    AND CTO.ACCKINDID = CTC.ACCKINDID
   AND SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) = SUBSTR(CTC.TRNDOCNO, 0, INSTR(CTC.TRNDOCNO, '_'))
   AND CTO.PERIODID <> CTC.PERIODID
   AND CTO.PROCESSID <> CTC.PROCESSID
 )
WHERE EXISTS (
  SELECT NULL
    FROM CUSTOMER_TRANSACTIONS_20070716 CTO
    WHERE CTO.PERIODID = '2005-2006'
    AND CTO.PROCESSID = '200612'
    AND CTO.COMPANYID = CTC.COMPANYID
    AND CTO.CUSTOMERID = CTC.CUSTOMERID
    AND CTO.APPOINTOFCAID = CTC.APPOINTOFCAID
    AND CTO.ACCKINDID = CTC.ACCKINDID
   AND SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) = SUBSTR(CTC.TRNDOCNO, 0, INSTR(CTC.TRNDOCNO, '_'))
   AND CTO.PERIODID <> CTC.PERIODID
   AND CTO.PROCESSID <> CTC.PROCESSID

);
0
 
LVL 12

Accepted Solution

by:
jwahl earned 1800 total points
ID: 20020833
or shorter:

UPDATE CUSTOMER_TRANSACTIONS CTC
SET PERIODID = '2005-2006,  
       PROCESSID  = '200612'
WHERE EXISTS (
  SELECT NULL
    FROM CUSTOMER_TRANSACTIONS_20070716 CTO
    WHERE CTO.PERIODID = '2005-2006'
    AND CTO.PROCESSID = '200612'
    AND CTO.COMPANYID = CTC.COMPANYID
    AND CTO.CUSTOMERID = CTC.CUSTOMERID
    AND CTO.APPOINTOFCAID = CTC.APPOINTOFCAID
    AND CTO.ACCKINDID = CTC.ACCKINDID
   AND SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) = SUBSTR(CTC.TRNDOCNO, 0, INSTR(CTC.TRNDOCNO, '_'))
   AND CTO.PERIODID <> CTC.PERIODID
   AND CTO.PROCESSID <> CTC.PROCESSID
);
0
 

Author Comment

by:gla
ID: 20020976
when I execute the following query the sql statement return 1 row
SELECT CTC.COMPANYID,
CTC.CUSTOMERID,
CTC.APPOINTOFCAID,
CTC.ACCKINDID,
CTC.TRNDATE,
SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) ODOCNO,
CTC.TRNDOCNO,
CTO.TRNDOCNO OTRNDOCNO,
CTO.PERIODID OPERIODID,
CTO.PROCESSID OPROCESSID,
CTC.PERIODID CPERIODID,
CTC.PROCESSID CPROCESSID
FROM CUSTOMER_TRANSACTIONS_20070716 CTO, CUSTOMER_TRANSACTIONS CTC
WHERE CTO.PERIODID = '2005-2006'
AND CTO.PROCESSID = '200612'
AND CTO.COMPANYID = CTC.COMPANYID
AND CTO.CUSTOMERID = CTC.CUSTOMERID
AND CTO.APPOINTOFCAID = CTC.APPOINTOFCAID
AND CTO.APPOINTOFCAID = 104392
AND CTO.ACCKINDID = CTC.ACCKINDID
AND SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) = SUBSTR(CTC.TRNDOCNO, 0, INSTR(CTC.TRNDOCNO, '_'))
AND CTO.PERIODID <> CTC.PERIODID
AND CTO.PROCESSID <> CTC.PROCESSID

when I execute the following update query

UPDATE CUSTOMER_TRANSACTIONS CTC
SET PERIODID = '2005-2006,  
       PROCESSID  = '200612'
WHERE EXISTS (
  SELECT NULL
    FROM CUSTOMER_TRANSACTIONS_20070716 CTO
    WHERE CTO.PERIODID = '2005-2006'
    AND CTO.PROCESSID = '200612'
    AND CTO.COMPANYID = CTC.COMPANYID
    AND CTO.CUSTOMERID = CTC.CUSTOMERID
    AND CTO.APPOINTOFCAID = CTC.APPOINTOFCAID
      AND CTO.APPOINTOFCAID = 104392
    AND CTO.ACCKINDID = CTC.ACCKINDID
   AND SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) = SUBSTR(CTC.TRNDOCNO, 0, INSTR(CTC.TRNDOCNO, '_'))
   AND CTO.PERIODID <> CTC.PERIODID
   AND CTO.PROCESSID <> CTC.PROCESSID
);

that run for long time and not end after 20'. This is  normal?
0
 
LVL 9

Expert Comment

by:konektor
ID: 20021019
queries probably has different execution plans. if you have computed statitics probably select uses some hash join, update nested loop.
isn't here some locks ?

you can check with adding FOR UPDATE NOWAIT into your select statement. if it fires ora-54 someoe else has locked rows you are going to update
0
 
LVL 12

Assisted Solution

by:jwahl
jwahl earned 1800 total points
ID: 20021059
it depends also on the indexes you are using.
if CTO.APPOINTOFCAID is indexes, this will be faster:

UPDATE CUSTOMER_TRANSACTIONS CTC
SET PERIODID = '2005-2006,  
       PROCESSID  = '200612'
WHERE EXISTS (
  SELECT NULL
    FROM CUSTOMER_TRANSACTIONS_20070716 CTO
    WHERE CTO.PERIODID = '2005-2006'
    AND CTO.PROCESSID = '200612'
    AND CTO.COMPANYID = CTC.COMPANYID
    AND CTO.CUSTOMERID = CTC.CUSTOMERID
    AND CTO.APPOINTOFCAID = CTC.APPOINTOFCAID
      AND CTO.APPOINTOFCAID = 104392
    AND CTO.ACCKINDID = CTC.ACCKINDID
   AND SUBSTR(CTO.TRNDOCNO, 0, INSTR(CTO.TRNDOCNO, '_')) = SUBSTR(CTC.TRNDOCNO, 0, INSTR(CTC.TRNDOCNO, '_'))
   AND CTO.PERIODID <> CTC.PERIODID
   AND CTO.PROCESSID <> CTC.PROCESSID
)
AND (CTC.COMPANYID, CTC.CUSTOMERID, CTC.APPOINTOFCAID, CTC.ACCKINDID) IN (
       SELECT CTO.COMPANYID, CTO.CUSTOMERID, CTO.APPOINTOFCAID, CT=.ACCKINDID
    FROM CUSTOMER_TRANSACTIONS_20070716 CTO
    WHERE CTO.APPOINTOFCAID = 104392
    )
;
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question