Sum the amount and then delete the second row when its duplicate in Oracle

I am trying to do below two things for this table.

enter image description here

There are 3 Unique row if we check by EMP_Id, Year and Type. I need to add sum of Bonus_Amt only when it is duplicate. For ex,
For EMP_ID = 1708301 Bonus_AMT should be = 22196 + 3036.
It should not do anything for EMP_ID = 642416.

Once the sum is done delete the second row but do check carefully that Bonus_Amt should be less then other one in duplicate record.

The table record should be like below when step 1 and step 2 is performed.

enter image description here

I did try something like below for Point 1 but this is giving me row number 2 and not row number 1. If i change t.rn = 1 then it is picking all records even the one which is not duplicate.

SELECT t.*

FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.EMP_Id ORDER BY o.Year) rn

     FROM Emp_Stat o where o.Year='2018' AND o.Type= 'Check' 

     ) t 

WHERE t.rn  > 1

Here UniqueId does not matter. The catch is that only the record which is duplicate should be sum and then the one which is not added should be deleted. Also there is a column lst_update_time which should update to sysdate only for the duplicate row and not for any other record.

edited Nov 22 '18 at 8:09

asked Nov 21 '18 at 10:05

Rahul

2510

How do you decide which Unique_id should remain after deletion? Or does it not matter?

– Kaushik Nayak
Nov 21 '18 at 13:28

It does not matter. The catch is that only the record which is duplicate should be sum and then the one which is not added should be deleted. Also there is a column lst_update_time which should update to sysdate only for the duplicate row and not for any other record.

– Rahul
Nov 22 '18 at 8:08

add a comment |

I am trying to do below two things for this table.

enter image description here

There are 3 Unique row if we check by EMP_Id, Year and Type. I need to add sum of Bonus_Amt only when it is duplicate. For ex,
For EMP_ID = 1708301 Bonus_AMT should be = 22196 + 3036.
It should not do anything for EMP_ID = 642416.

Once the sum is done delete the second row but do check carefully that Bonus_Amt should be less then other one in duplicate record.

The table record should be like below when step 1 and step 2 is performed.

enter image description here

I did try something like below for Point 1 but this is giving me row number 2 and not row number 1. If i change t.rn = 1 then it is picking all records even the one which is not duplicate.

SELECT t.*

FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.EMP_Id ORDER BY o.Year) rn

     FROM Emp_Stat o where o.Year='2018' AND o.Type= 'Check' 

     ) t 

WHERE t.rn  > 1

edited Nov 22 '18 at 8:09

asked Nov 21 '18 at 10:05

Rahul

2510

How do you decide which Unique_id should remain after deletion? Or does it not matter?

– Kaushik Nayak
Nov 21 '18 at 13:28

It does not matter. The catch is that only the record which is duplicate should be sum and then the one which is not added should be deleted. Also there is a column lst_update_time which should update to sysdate only for the duplicate row and not for any other record.

– Rahul
Nov 22 '18 at 8:08

add a comment |

I am trying to do below two things for this table.

enter image description here

There are 3 Unique row if we check by EMP_Id, Year and Type. I need to add sum of Bonus_Amt only when it is duplicate. For ex,
For EMP_ID = 1708301 Bonus_AMT should be = 22196 + 3036.
It should not do anything for EMP_ID = 642416.

Once the sum is done delete the second row but do check carefully that Bonus_Amt should be less then other one in duplicate record.

The table record should be like below when step 1 and step 2 is performed.

enter image description here

I did try something like below for Point 1 but this is giving me row number 2 and not row number 1. If i change t.rn = 1 then it is picking all records even the one which is not duplicate.

SELECT t.*

FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.EMP_Id ORDER BY o.Year) rn

     FROM Emp_Stat o where o.Year='2018' AND o.Type= 'Check' 

     ) t 

WHERE t.rn  > 1

edited Nov 22 '18 at 8:09

asked Nov 21 '18 at 10:05

Rahul

2510

I am trying to do below two things for this table.

enter image description here

There are 3 Unique row if we check by EMP_Id, Year and Type. I need to add sum of Bonus_Amt only when it is duplicate. For ex,
For EMP_ID = 1708301 Bonus_AMT should be = 22196 + 3036.
It should not do anything for EMP_ID = 642416.

Once the sum is done delete the second row but do check carefully that Bonus_Amt should be less then other one in duplicate record.

The table record should be like below when step 1 and step 2 is performed.

enter image description here

I did try something like below for Point 1 but this is giving me row number 2 and not row number 1. If i change t.rn = 1 then it is picking all records even the one which is not duplicate.

SELECT t.*

FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.EMP_Id ORDER BY o.Year) rn

     FROM Emp_Stat o where o.Year='2018' AND o.Type= 'Check' 

     ) t 

WHERE t.rn  > 1

database oracle duplicates

edited Nov 22 '18 at 8:09

asked Nov 21 '18 at 10:05

Rahul

2510

edited Nov 22 '18 at 8:09

asked Nov 21 '18 at 10:05

Rahul

2510

edited Nov 22 '18 at 8:09

asked Nov 21 '18 at 10:05

Rahul

2510

asked Nov 21 '18 at 10:05

Rahul

2510

asked Nov 21 '18 at 10:05

Rahul

2510

How do you decide which Unique_id should remain after deletion? Or does it not matter?

– Kaushik Nayak
Nov 21 '18 at 13:28

It does not matter. The catch is that only the record which is duplicate should be sum and then the one which is not added should be deleted. Also there is a column lst_update_time which should update to sysdate only for the duplicate row and not for any other record.

– Rahul
Nov 22 '18 at 8:08

add a comment |

How do you decide which Unique_id should remain after deletion? Or does it not matter?

– Kaushik Nayak
Nov 21 '18 at 13:28

It does not matter. The catch is that only the record which is duplicate should be sum and then the one which is not added should be deleted. Also there is a column lst_update_time which should update to sysdate only for the duplicate row and not for any other record.

– Rahul
Nov 22 '18 at 8:08

How do you decide which Unique_id should remain after deletion? Or does it not matter?

– Kaushik Nayak
Nov 21 '18 at 13:28

It does not matter. The catch is that only the record which is duplicate should be sum and then the one which is not added should be deleted. Also there is a column lst_update_time which should update to sysdate only for the duplicate row and not for any other record.

– Rahul
Nov 22 '18 at 8:08

add a comment |

2 Answers
2

active

oldest

votes

You can do this in a single MERGE statement, using both update and delete in the when matched clause, like so:

merge into t1 tgt

  using (select unique_id,

                emp_id,

                sum(bonus_amt) over (partition by emp_id, year, type) new_bonus_amt,

                year,

                type,

                count(*) over (partition by emp_id, year, type) grp_count,

                row_number() over (partition by emp_id, year, type order by bonus_amt desc) rn

         from   t1) src

  on (tgt.unique_id = src.unique_id and src.grp_count > 1)

when matched then

   update set tgt.bonus_amt = src.new_bonus_amt,

              tgt.last_update_time = sysdate

   delete where rn != 1;

Demo DBFiddle

This works by finding the sum of the bonus_amt for each group (doesn't matter if the group has a single row or multiple) and identifying the first row in the group (that is, the one with the highest bonus_amt), so we know which row to keep.

Then, the we use that source set of data in the merge statement to update the bonus_amt of every row (you need to update every row, otherwise the delete won't "see" the un-updated rows) before deleting all but the first row in each group.

edited Nov 22 '18 at 9:33

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

This query is good but its my bad i didn't mention entire requirement. There is Last_Update_Time column also. Problem is that if i add set Last_Update_Time=sysdate in update line it will update all row despite its duplicate or not. Adding rn != 1 will not help either because it will update second row and then in delete it will remove it.... Can you advice on this please.. I need to update the timestamp of duplicate one only.

– Rahul
Nov 22 '18 at 7:59

Does it matter if all the rows have their last_update_time amended if the duplicate rows are going to be deleted anyway? Or are you saying that you don't want the duplicated rows to be deleted after all?

– Boneist
Nov 22 '18 at 8:31

There are duplicate and then unique records also in this table. Only row with duplicate row should have there last_update_time updated and not the Unique record one and Yes, for audit purpose it lst_updt_time does matter. As per the solution provided it is updating other column and deleting the one which is duplicate.. this is correct only thing missing is last_update_time for duplicate rows only.

– Rahul
Nov 22 '18 at 9:09

I would have thought that for audit purposes, you'd need to know that the unique row was also updated? I.e. update the last_update_time column for all rows, given you're updating the bonus_amt for all rows?

– Boneist
Nov 22 '18 at 9:25

1

Ok, I've updated both my answer (to include a count of the group, so that we only merge where the count is > 1), and the dbfiddle demo.

– Boneist
Nov 22 '18 at 9:37

|
show 4 more comments

I like the answer with merge statement;
Just wanted to suggest more transparent solution :

Start

Select sum grouped by the UK (as in that merge statement) and insert results into the auxiliary table. - You will have one record per UK

Delete ALL records from the base table

Insert into base table from aux .table

commit work;

answered Nov 21 '18 at 13:34

evgeniya makarova

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409593%2fsum-the-amount-and-then-delete-the-second-row-when-its-duplicate-in-oracle%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

You can do this in a single MERGE statement, using both update and delete in the when matched clause, like so:

merge into t1 tgt

  using (select unique_id,

                emp_id,

                sum(bonus_amt) over (partition by emp_id, year, type) new_bonus_amt,

                year,

                type,

                count(*) over (partition by emp_id, year, type) grp_count,

                row_number() over (partition by emp_id, year, type order by bonus_amt desc) rn

         from   t1) src

  on (tgt.unique_id = src.unique_id and src.grp_count > 1)

when matched then

   update set tgt.bonus_amt = src.new_bonus_amt,

              tgt.last_update_time = sysdate

   delete where rn != 1;

Demo DBFiddle

edited Nov 22 '18 at 9:33

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

This query is good but its my bad i didn't mention entire requirement. There is Last_Update_Time column also. Problem is that if i add set Last_Update_Time=sysdate in update line it will update all row despite its duplicate or not. Adding rn != 1 will not help either because it will update second row and then in delete it will remove it.... Can you advice on this please.. I need to update the timestamp of duplicate one only.

– Rahul
Nov 22 '18 at 7:59

Does it matter if all the rows have their last_update_time amended if the duplicate rows are going to be deleted anyway? Or are you saying that you don't want the duplicated rows to be deleted after all?

– Boneist
Nov 22 '18 at 8:31

There are duplicate and then unique records also in this table. Only row with duplicate row should have there last_update_time updated and not the Unique record one and Yes, for audit purpose it lst_updt_time does matter. As per the solution provided it is updating other column and deleting the one which is duplicate.. this is correct only thing missing is last_update_time for duplicate rows only.

– Rahul
Nov 22 '18 at 9:09

I would have thought that for audit purposes, you'd need to know that the unique row was also updated? I.e. update the last_update_time column for all rows, given you're updating the bonus_amt for all rows?

– Boneist
Nov 22 '18 at 9:25

1

Ok, I've updated both my answer (to include a count of the group, so that we only merge where the count is > 1), and the dbfiddle demo.

– Boneist
Nov 22 '18 at 9:37

|
show 4 more comments

You can do this in a single MERGE statement, using both update and delete in the when matched clause, like so:

merge into t1 tgt

  using (select unique_id,

                emp_id,

                sum(bonus_amt) over (partition by emp_id, year, type) new_bonus_amt,

                year,

                type,

                count(*) over (partition by emp_id, year, type) grp_count,

                row_number() over (partition by emp_id, year, type order by bonus_amt desc) rn

         from   t1) src

  on (tgt.unique_id = src.unique_id and src.grp_count > 1)

when matched then

   update set tgt.bonus_amt = src.new_bonus_amt,

              tgt.last_update_time = sysdate

   delete where rn != 1;

Demo DBFiddle

edited Nov 22 '18 at 9:33

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

This query is good but its my bad i didn't mention entire requirement. There is Last_Update_Time column also. Problem is that if i add set Last_Update_Time=sysdate in update line it will update all row despite its duplicate or not. Adding rn != 1 will not help either because it will update second row and then in delete it will remove it.... Can you advice on this please.. I need to update the timestamp of duplicate one only.

– Rahul
Nov 22 '18 at 7:59

Does it matter if all the rows have their last_update_time amended if the duplicate rows are going to be deleted anyway? Or are you saying that you don't want the duplicated rows to be deleted after all?

– Boneist
Nov 22 '18 at 8:31

There are duplicate and then unique records also in this table. Only row with duplicate row should have there last_update_time updated and not the Unique record one and Yes, for audit purpose it lst_updt_time does matter. As per the solution provided it is updating other column and deleting the one which is duplicate.. this is correct only thing missing is last_update_time for duplicate rows only.

– Rahul
Nov 22 '18 at 9:09

I would have thought that for audit purposes, you'd need to know that the unique row was also updated? I.e. update the last_update_time column for all rows, given you're updating the bonus_amt for all rows?

– Boneist
Nov 22 '18 at 9:25

1

Ok, I've updated both my answer (to include a count of the group, so that we only merge where the count is > 1), and the dbfiddle demo.

– Boneist
Nov 22 '18 at 9:37

|
show 4 more comments

You can do this in a single MERGE statement, using both update and delete in the when matched clause, like so:

merge into t1 tgt

  using (select unique_id,

                emp_id,

                sum(bonus_amt) over (partition by emp_id, year, type) new_bonus_amt,

                year,

                type,

                count(*) over (partition by emp_id, year, type) grp_count,

                row_number() over (partition by emp_id, year, type order by bonus_amt desc) rn

         from   t1) src

  on (tgt.unique_id = src.unique_id and src.grp_count > 1)

when matched then

   update set tgt.bonus_amt = src.new_bonus_amt,

              tgt.last_update_time = sysdate

   delete where rn != 1;

Demo DBFiddle

edited Nov 22 '18 at 9:33

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

You can do this in a single MERGE statement, using both update and delete in the when matched clause, like so:

merge into t1 tgt

  using (select unique_id,

                emp_id,

                sum(bonus_amt) over (partition by emp_id, year, type) new_bonus_amt,

                year,

                type,

                count(*) over (partition by emp_id, year, type) grp_count,

                row_number() over (partition by emp_id, year, type order by bonus_amt desc) rn

         from   t1) src

  on (tgt.unique_id = src.unique_id and src.grp_count > 1)

when matched then

   update set tgt.bonus_amt = src.new_bonus_amt,

              tgt.last_update_time = sysdate

   delete where rn != 1;

Demo DBFiddle

edited Nov 22 '18 at 9:33

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

edited Nov 22 '18 at 9:33

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

answered Nov 21 '18 at 10:54

Boneist

18.4k11228

This query is good but its my bad i didn't mention entire requirement. There is Last_Update_Time column also. Problem is that if i add set Last_Update_Time=sysdate in update line it will update all row despite its duplicate or not. Adding rn != 1 will not help either because it will update second row and then in delete it will remove it.... Can you advice on this please.. I need to update the timestamp of duplicate one only.

– Rahul
Nov 22 '18 at 7:59

Does it matter if all the rows have their last_update_time amended if the duplicate rows are going to be deleted anyway? Or are you saying that you don't want the duplicated rows to be deleted after all?

– Boneist
Nov 22 '18 at 8:31

There are duplicate and then unique records also in this table. Only row with duplicate row should have there last_update_time updated and not the Unique record one and Yes, for audit purpose it lst_updt_time does matter. As per the solution provided it is updating other column and deleting the one which is duplicate.. this is correct only thing missing is last_update_time for duplicate rows only.

– Rahul
Nov 22 '18 at 9:09

I would have thought that for audit purposes, you'd need to know that the unique row was also updated? I.e. update the last_update_time column for all rows, given you're updating the bonus_amt for all rows?

– Boneist
Nov 22 '18 at 9:25

1

Ok, I've updated both my answer (to include a count of the group, so that we only merge where the count is > 1), and the dbfiddle demo.

– Boneist
Nov 22 '18 at 9:37

|
show 4 more comments

This query is good but its my bad i didn't mention entire requirement. There is Last_Update_Time column also. Problem is that if i add set Last_Update_Time=sysdate in update line it will update all row despite its duplicate or not. Adding rn != 1 will not help either because it will update second row and then in delete it will remove it.... Can you advice on this please.. I need to update the timestamp of duplicate one only.

– Rahul
Nov 22 '18 at 7:59

Does it matter if all the rows have their last_update_time amended if the duplicate rows are going to be deleted anyway? Or are you saying that you don't want the duplicated rows to be deleted after all?

– Boneist
Nov 22 '18 at 8:31

There are duplicate and then unique records also in this table. Only row with duplicate row should have there last_update_time updated and not the Unique record one and Yes, for audit purpose it lst_updt_time does matter. As per the solution provided it is updating other column and deleting the one which is duplicate.. this is correct only thing missing is last_update_time for duplicate rows only.

– Rahul
Nov 22 '18 at 9:09

I would have thought that for audit purposes, you'd need to know that the unique row was also updated? I.e. update the last_update_time column for all rows, given you're updating the bonus_amt for all rows?

– Boneist
Nov 22 '18 at 9:25

1

Ok, I've updated both my answer (to include a count of the group, so that we only merge where the count is > 1), and the dbfiddle demo.

– Boneist
Nov 22 '18 at 9:37

This query is good but its my bad i didn't mention entire requirement. There is Last_Update_Time column also. Problem is that if i add set Last_Update_Time=sysdate in update line it will update all row despite its duplicate or not. Adding rn != 1 will not help either because it will update second row and then in delete it will remove it.... Can you advice on this please.. I need to update the timestamp of duplicate one only.

– Rahul
Nov 22 '18 at 7:59

Does it matter if all the rows have their last_update_time amended if the duplicate rows are going to be deleted anyway? Or are you saying that you don't want the duplicated rows to be deleted after all?

– Boneist
Nov 22 '18 at 8:31

There are duplicate and then unique records also in this table. Only row with duplicate row should have there last_update_time updated and not the Unique record one and Yes, for audit purpose it lst_updt_time does matter. As per the solution provided it is updating other column and deleting the one which is duplicate.. this is correct only thing missing is last_update_time for duplicate rows only.

– Rahul
Nov 22 '18 at 9:09

I would have thought that for audit purposes, you'd need to know that the unique row was also updated? I.e. update the last_update_time column for all rows, given you're updating the bonus_amt for all rows?

– Boneist
Nov 22 '18 at 9:25

Ok, I've updated both my answer (to include a count of the group, so that we only merge where the count is > 1), and the dbfiddle demo.

– Boneist
Nov 22 '18 at 9:37

|
show 4 more comments

I like the answer with merge statement;
Just wanted to suggest more transparent solution :

Start

Select sum grouped by the UK (as in that merge statement) and insert results into the auxiliary table. - You will have one record per UK

Delete ALL records from the base table

Insert into base table from aux .table

commit work;

answered Nov 21 '18 at 13:34

evgeniya makarova

add a comment |

I like the answer with merge statement;
Just wanted to suggest more transparent solution :

Start

Select sum grouped by the UK (as in that merge statement) and insert results into the auxiliary table. - You will have one record per UK

Delete ALL records from the base table

Insert into base table from aux .table

commit work;

answered Nov 21 '18 at 13:34

evgeniya makarova

add a comment |

I like the answer with merge statement;
Just wanted to suggest more transparent solution :

Start

Select sum grouped by the UK (as in that merge statement) and insert results into the auxiliary table. - You will have one record per UK

Delete ALL records from the base table

Insert into base table from aux .table

commit work;

answered Nov 21 '18 at 13:34

evgeniya makarova

I like the answer with merge statement;
Just wanted to suggest more transparent solution :

Start

Select sum grouped by the UK (as in that merge statement) and insert results into the auxiliary table. - You will have one record per UK

Delete ALL records from the base table

Insert into base table from aux .table

commit work;

answered Nov 21 '18 at 13:34

evgeniya makarova

answered Nov 21 '18 at 13:34

evgeniya makarova

answered Nov 21 '18 at 13:34

evgeniya makarova

answered Nov 21 '18 at 13:34

evgeniya makarova

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu