Below is my Query, When i am trying to update i am getting below error.
UPDATE PS_CT_IQN_ACC_STG STG
SET (STG.STANDARD_RATE , STG.STANDARD_HOURS ,STG.CURRENCY_CD ) = (
SELECT RT.STANDARD_RATE
,RT.HOURS_PER_DAY
,RT.CURRENCY_CD
FROM PS_CTS_IQN_BU_RATE RT
WHERE RT.BUSINESS_UNIT = STG.BUSINESS_UNIT
AND STG.PROCESS_INSTANCE = 21092725
AND STG.STANDARD_RATE = 0
AND STG.STANDARD_HOURS = 0)
Getting Below Error
Table and Insert Scripts:
CREATE TABLE PS_CTS_IQN_BU_RATE (SEQUENCE_NUMBER INTEGER NOT NULL,
BUSINESS_UNIT VARCHAR2(5) NOT NULL,
HOURS_PER_DAY INTEGER NOT NULL,
STANDARD_RATE DECIMAL(15, 2) NOT NULL,
CURRENCY_CD VARCHAR2(3) NOT NULL) ;
Insert into PS_CTS_IQN_BU_RATE (SEQUENCE_NUMBER,BUSINESS_UNIT,HOURS_PER_DAY,STANDARD_RATE,CURRENCY_CD) values (0,'US416',0,67,'AOA');
Insert into PS_CTS_IQN_BU_RATE (SEQUENCE_NUMBER,BUSINESS_UNIT,HOURS_PER_DAY,STANDARD_RATE,CURRENCY_CD) values (0,'US410',1,32,'ADP');
Insert into PS_CTS_IQN_BU_RATE (SEQUENCE_NUMBER,BUSINESS_UNIT,HOURS_PER_DAY,STANDARD_RATE,CURRENCY_CD) values (0,'HQ179',12,45,'AOA');
Insert into PS_CTS_IQN_BU_RATE (SEQUENCE_NUMBER,BUSINESS_UNIT,HOURS_PER_DAY,STANDARD_RATE,CURRENCY_CD) values (0,'HQ180',1,78,'AMD');
Insert into PS_CTS_IQN_BU_RATE (SEQUENCE_NUMBER,BUSINESS_UNIT,HOURS_PER_DAY,STANDARD_RATE,CURRENCY_CD) values (0,'HQ217',1,45,'AMD');
Insert into PS_CTS_IQN_BU_RATE (SEQUENCE_NUMBER,BUSINESS_UNIT,HOURS_PER_DAY,STANDARD_RATE,CURRENCY_CD) values (0,'HQ170',1,45,'USD');
Table 2 :
CREATE TABLE PS_CT_IQN_ACC_STG (PROCESS_INSTANCE INTEGER NOT NULL,
BUSINESS_UNIT VARCHAR2(5) NOT NULL,
STANDARD_HOURS INTEGER NOT NULL,
STANDARD_RATE DECIMAL(15, 2) NOT NULL,
CURRENCY_CD VARCHAR2(3) NOT NULL) ;
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'CDUSA',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'CL607',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'CLIND',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'EDIND',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'HQ179',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'HQ180',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'HQ217',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'HQ230',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'IN208',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'MA305',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'US410',0,0,' ');
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,BUSINESS_UNIT,STANDARD_HOURS,STANDARD_RATE,CURRENCY_CD) values (21092725,'US440',0,0,' ');