CODEWITHSUNDEEP
mysqlsql
Varinder Verma
Varinder Verma

Reputation: 13

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

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

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

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

Upvotes: 0

Related Questions