gla
asked on
oracle sql update join two tables
I hava the following update query:
UPDATE CUSTOMER_TRANSACTIONS CTC
SET CTC.PERIODID = '2005-2006', CTC.PROCESSID = '200612'
FROM CUSTOMER_TRANSACTIONS_2007 0716 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?
UPDATE CUSTOMER_TRANSACTIONS CTC
SET CTC.PERIODID = '2005-2006', CTC.PROCESSID = '200612'
FROM CUSTOMER_TRANSACTIONS_2007
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_2007
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_2007
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?