Delete Duplicate values from specific column in mysql table based on query to other column

Question

I am having one table in SQL Database where I record customer wise sales for specific products. I have monthly target for each product like as below

Product A - 50 pcs

Now in my table I am seeing customer wise sales and the monthly product sale target which is common.

Customer    Product     MonthlyTargetQty
Customer A  Product 1       50
Customer B  Product 1       50
Customer C  Product 1       50
Customer D  Product 1       50

I want to keep only distinct value in MonthlyTargetQty Column and do not want to delete Product name which is repeating in Product Column. Please help with a query

How I want it is : -

Customer    Product     MonthlyTargetQty
Customer A  Product 1       50
Customer B  Product 1       0
Customer C  Product 1       0
Customer D  Product 1       0

This looks more like a presentation issue that should be handled in the presentation layer. Gordon's answer works, though. — The Impaler
– The Impaler, Commented Jul 28, 2020 at 14:20
@VarinderVerma . . . Your title is quite misleading. You are not deleting anything, and in particular, not any rows. — Gordon Linoff
– Gordon Linoff, Commented Jul 28, 2020 at 14:25

Gordon Linoff · Accepted Answer · 2020-07-28 14:55:48Z

1

You seem to want:

select customer, product,
       (case when row_number() over (partition by product order by customer) = 1 then monthlytargetqty end) as monthlytargetqty
from t
order by product, customer;

This uses row_number() to define the first row for each customer and then a case expression to keep the value you want on that row. Note that the order by is consistent with the partition by/order by for row_number().

EDIT:

If you want to update the existing table -- which seems like a really bad idea to me -- you can do:

update t join
       (select product, min(customer) as min_customer
        from t
        group by product
       ) tt
       on t.product = tt.product and t.customer <> tt.min_customer
    set monthlytargetqty = 0;

edited Jul 28, 2020 at 14:55

answered Jul 28, 2020 at 14:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Varinder Verma Over a year ago

Thanks for the guidance. it worked well but it is doing select and not updating the existing table. can you guide me with the Update Table query ?

update ProductAnalysisTable set MontylyTargetQty= case when row_number() over (partition by Product order by customer) = 1 then MontylyTargetQty end

Gordon Linoff Over a year ago

@VarinderVerma . . . That seems like a bad idea to me, but I edited the answer with an update to do that. I'm not a fan in overwriting data, especially when a simple query does what you want.

Varinder Verma Over a year ago

Thanks. It worked for me in another query and this is a very useful code. Thanks again

Gordon Linoff Over a year ago

@VarinderVerma . . . I'm surprised you didn't accept this answer.

Zaynul Abadin Tuhin · Accepted Answer · 2020-07-28 14:53:46Z

0

from the comment it seems you want update I added with update

    with cte as
    (
    select customer, product,
           (case when row_number() over (partition by product order by customer) = 1 then monthlytargetqty end) as monthlytargetqty
    from t
    )
  update a 
  set a.MontylyTargetQty= b.monthlytargetqty
  from ProductAnalysisTable a join cte on
a.customer=cte.customer and a.product=b.product

btw 1st part is sir @gordon so accept his answer

answered Jul 28, 2020 at 14:53

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

2 Comments

Varinder Verma Over a year ago

@GordonLinoff. It did work for me. I am not sure what is the issue but this code did the same what I needed.

Gordon Linoff Over a year ago

@VarinderVerma . . . Fix the tag on your question. I don't know why Zaynul answered with syntax inconsistent with the question tag, but as it stands, this does not answer the question that you asked.

Collectives™ on Stack Overflow

Delete Duplicate values from specific column in mysql table based on query to other column

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related