CODEWITHSUNDEEP
sqloracle-database
user2570205
user2570205

Reputation: 137

Oracle merge into delete from different table

I have the delete statement in the last. Essentially , I want to delete only if merge returned rows or matched. Is it possible to do in one single merge into statement ?

MERGE INTO ${TABLE_NAME} T
USING (
    SELECT
    ERRORCODE, PROCESSINGFILENAME,
    RECORDNUMBER from ERROR_UPLOAD_T
 ) TMP
ON (T.RECORDNUMBER = TMP.RECORDNUMBER and
T.PROCESSINGFILENAME= TMP.PROCESSINGFILENAME and
T.PROCESSINGFILENAME='${new_err_xml}')
WHEN MATCHED THEN
UPDATE SET
    T.STATUS = 'ERROR',
    T.ERRORSOURCE = 'BRM',
    T.ERRORCODE = TMP.ERRORCODE

DELETE from ERROR_UPLOAD_T WHERE
PROCESSINGFILENAME like '${new_err_xml}';

If not possible this way, please tell me better way to do this. This particular statement is called in shell script using sqlplus.

EDIT: I tried this way and it looks to working.

BEGIN

MERGE INTO ${TABLE_NAME} T
USING (
    SELECT
    ERRORCODE, PROCESSINGFILENAME,
    RECORDNUMBER from ERROR_UPLOAD_T
 ) TMP
ON (T.RECORDNUMBER = TMP.RECORDNUMBER and
T.PROCESSINGFILENAME= TMP.PROCESSINGFILENAME and
T.PROCESSINGFILENAME='${new_err_xml}')
WHEN MATCHED THEN
UPDATE SET
    T.STATUS = 'ERROR',
    T.ERRORSOURCE = 'BRM',
    T.ERRORCODE = TMP.ERRORCODE;

if (sql%rowcount > 0) then
    DELETE from ERROR_UPLOAD_T WHERE
    PROCESSINGFILENAME like '${new_err_xml}';
end if;

COMMIT;
END;
/

Upvotes: 0

Views: 629

Answers (2)

Alex Poole
Alex Poole

Reputation: 191520

No, you can't delete from the source table (from the using clause) or elsewhere as part of the merge itself. From the documentation:

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.

So you can only delete from the destination table, which isn't what you want.

Upvotes: 1

user2570205
user2570205

Reputation: 137

I managed to achieve the result by changing the query in following manner.

BEGIN
MERGE INTO ${TABLE_NAME} T
USING (
    SELECT
    ERRORCODE, PROCESSINGFILENAME,
    RECORDNUMBER from ERROR_UPLOAD_T
 ) TMP
ON (T.RECORDNUMBER = TMP.RECORDNUMBER and
T.PROCESSINGFILENAME= TMP.PROCESSINGFILENAME and
T.PROCESSINGFILENAME='${new_err_xml}')
WHEN MATCHED THEN
UPDATE SET
    T.STATUS = 'ERROR',
    T.ERRORSOURCE = 'BRM',
    T.ERRORCODE = TMP.ERRORCODE;

if (sql%rowcount > 0) then
    DELETE from ERROR_UPLOAD_T WHERE
    PROCESSINGFILENAME like '${new_err_xml}';
end if;

COMMIT;
END;
/

Upvotes: 1

Related Questions