I have written an Oracle procedure if count is greater than 0 then update the table, but what is happening is it's updating all the records of the table with that one record.
Here is the query
SELECT COUNT(*) INTO T_CNT_1 FROM IPCOLO_BILLING_MASTER WHERE SAP_ID = P_SAP_ID;
IF T_CNT_1 > 0 THEN
UPDATE IPCOLO_BILLING_MASTER t1
SET (ID ,
CMP ,
SAP_ID ,
ID_OD_COUNTCHANGE ,
ID_OD_CHANGEDDATE ,
RRH_COUNTCHANGE ,
RRH_CHANGEDDATE ,
TENANCY_COUNTCHANGE ,
TENANCY_CHANGEDDATE ,
RFS_DATE ,
RFE1_DATE ,
INFRA_PROVIDER ,
IP_COLO_SITEID ,
SITE_NAME ,
R4GSTATE ,
MW_INSTALLED ,
DG_NONDG ,
EB_NONEB ,
TOWER_TYPE ,
VENDOR_CODE ,
RFCDATE ,
POLITICAL_STATE_NAME ,
POLITICAL_STATE_CODE ,
SITE_DROP_DATE ,
CITY_NAME ,
NEID ,
FACILITY_LATITUDE ,
FACILITY_LONGITUDE ,
RJ_STRUCTURE_TYPE ,
RJ_JC_NAME ,
RJ_JC_CODE ,
COMPANY_CODE ,
BLCHAIN_RESP_MSG_MASTER ,
BLCHAIN_RESP_CODE_MASTER ,
SITE_ADDRESS ,
BLCHAIN_RESP_MSG_INCREMENTAL ,
BLCHAIN_RESP_CODE_INCREMENTAL ,
CREATED_BY ,
CREATED_DATE ,
SEL_CHANGED_VAL ,
CMM ,
FCA ,
LAST_UPDATED_BY ,
LAST_UPDATED_DATE ,
IS_AUTO_UPDATED ,
INITIATED_MANUAL_UPLOAD ,
RFS_DATE_5G ,
DROP_DATE_5G ,
OLT_COUNT ,
OLT_CHANGE_DATE ,
DIESEL_DOWNTIME_MINUTES ,
OVERALL_INFRA_OUTAGE_MINUTES ,
DIESEL_DOWNTIME_MIN_MY ,
OVERALL_INFRA_OUTAGE_MIN_MY ,
BK_RESPONSE_DATE ,
IS5GPRESENT) = (SELECT t2.ID ,
t2.CMP ,
t2.SAP_ID ,
t2.ID_OD_COUNTCHANGE ,
t2.ID_OD_CHANGEDDATE ,
t2.RRH_COUNTCHANGE ,
t2.RRH_CHANGEDDATE ,
t2.TENANCY_COUNTCHANGE ,
t2.TENANCY_CHANGEDDATE ,
t2.RFS_DATE ,
t2.RFE1_DATE ,
t2.INFRA_PROVIDER ,
t2.IP_COLO_SITEID ,
t2.SITE_NAME ,
t2.R4GSTATE ,
t2.MW_INSTALLED ,
t2.DG_NONDG ,
t2.EB_NONEB ,
t2.TOWER_TYPE ,
t2.VENDOR_CODE ,
t2.RFCDATE ,
t2.POLITICAL_STATE_NAME ,
t2.POLITICAL_STATE_CODE ,
t2.SITE_DROP_DATE ,
t2.CITY_NAME ,
t2.NEID ,
t2.FACILITY_LATITUDE ,
t2.FACILITY_LONGITUDE ,
t2.RJ_STRUCTURE_TYPE ,
t2.RJ_JC_NAME ,
t2.RJ_JC_CODE ,
t2.COMPANY_CODE ,
t2.BLCHAIN_RESP_MSG_MASTER ,
t2.BLCHAIN_RESP_CODE_MASTER ,
t2.SITE_ADDRESS ,
t2.BLCHAIN_RESP_MSG_INCREMENTAL ,
t2.BLCHAIN_RESP_CODE_INCREMENTAL ,
t2.CREATED_BY ,
t2.CREATED_DATE ,
t2.SEL_CHANGED_VAL ,
t2.CMM ,
t2.FCA ,
t2.LAST_UPDATED_BY ,
SYSDATE,
--t2.LAST_UPDATED_DATE ,
t2.IS_AUTO_UPDATED ,
t2.INITIATED_MANUAL_UPLOAD ,
t2.RFS_DATE_5G ,
t2.DROP_DATE_5G ,
t2.OLT_COUNT ,
t2.OLT_CHANGE_DATE ,
t2.DIESEL_DOWNTIME_MINUTES ,
t2.OVERALL_INFRA_OUTAGE_MINUTES ,
t2.DIESEL_DOWNTIME_MIN_MY ,
t2.OVERALL_INFRA_OUTAGE_MIN_MY ,
t2.BK_RESPONSE_DATE ,
t2.IS5GPRESENT
FROM TEMP_IPCOLO_BILLING_MST t2
WHERE t2.SAP_ID = P_SAP_ID
)
WHERE EXISTS (
SELECT 1
FROM TEMP_IPCOLO_BILLING_MST t2
WHERE t2.SAP_ID = P_SAP_ID );
Why is it updating all and I want to update that one single row only?
Well, the only condition you have on the update is
WHERE EXISTS (
SELECT 1
FROM TEMP_IPCOLO_BILLING_MST t2
WHERE t2.SAP_ID = P_SAP_ID );
So you're telling Oracle that you want every row in IPCOLO_BILLING_MASTER updated if there is any row in TEMP_IPCOLO_BILLING_MST with a SAP_ID of P_SAP_ID. Presumably, that is not what you actually want.
Since IPCOLO_BILLING_MASTER has a SAP_ID, my guess is that you want
WHERE EXISTS (
SELECT 1
FROM TEMP_IPCOLO_BILLING_MST t2
WHERE t2.SAP_ID = P_SAP_ID
AND t2.SAP_ID = t1.SAP_ID );
Presumably, you'd want similar logic in the SELECT statement you are using to get the values to update the table.
It may be clearer to re-write the update statement as a merge so that you don't have to duplicate this logic in two places.
If my guess is incorrect, please edit your question to include a reproducible test case. You aren't very specific about what you do want to happen so we have to make a lot of guesses about what your data looks like and what you really want your update statement to do.