change and merge multiple values on column if there is duplicate value in other columns

Question

Sorry, I'm new to SQL..I'm using Sybase Central

I need help, I'm currently stuck on data table from select statement (stored procedure) like this :

select case when product like 'A%' then 'Product A' when product like 'B%' 
then 'Product B' else 'Product C' end as 
'ProductGroup',product,invoice,customer from data_sales
group by product,invoice,customer

Results :

+--------------+---------+---------+---------+
| ProductGroup | Product | Invoice | Customer|
+--------------+---------+---------+---------+
| Product A    |   A1    | INV001  |   MR.A  |
| Product A    |   A1    | INV002  |   MR.B  |
| Product B    |   B1    | INV002  |   MR.B  |
| Product B    |   B1    | INV003  |   MR.C  |
+--------------+---------+---------+---------+

I want to merge and change the value into Product C in ProductGroup column, if there is duplicate values on Invoices or Customers columns

Results should be like this :

+--------------+--------+---------+
| ProductGroup | Invoice| Customer|
+--------------+--------+---------+
| Product A    | INV001 |   MR.A  |
| Product C    | INV002 |   MR.B  |
| Product B    | INV003 |   MR.C  |
+--------------+--------+---------+

I've been using case when and group by method, but it's still showing duplicate results

Any help would be really appreciated

Thank you

Thanks for replying, the logic is : when there is duplicate value on Customer or Invoice column then change the value in product group column into Product C — Untung Tanujaya, Jul 15 '19 at 07:58
`Invoices or Customers` If the customer is the same, the invoice value can be different. What value of Invoice must be in the output in that case ? In your sample both Invoice and Customer have the same value (INV002 and MR.B) — Conffusion, Jul 15 '19 at 08:46
@Conffusion thanks for asking, invoice value could be same or different with same customer, as long there is multiple product group on one invoice then the value should be product c — Untung Tanujaya, Jul 15 '19 at 09:30
@a_horse_with_no_name added new tag, i'm using sybase central, thanks for the hint — Untung Tanujaya, Jul 15 '19 at 09:30

Barbaros Özhan · Answer 1 · 2019-07-15T09:14:51.703

0

You can check the duplicates by having count(*)>1 clause with group by

update tab t
   set ProductGroup = 'Product C'
where exists (
                 select 1 
                   from tab tt
                  where exists ( select Invoice  
                                   from tab   
                                 where Invoice = tt.Invoice 
                                 group by Invoice 
                                having count(*)>1 )
                    or exists ( select Customer
                                  from tab
                                 where Customer = tt.Customer
                                 group by Customer 
                                having count(*)>1 )     )

edited Jul 15 '19 at 09:14

answered Jul 15 '19 at 07:58

Barbaros Özhan

59,113
10
31
55

Thank you for answering, is there possible if using update on select statement? because that data table was from select statement (stored procedure) my bad, i didnt mention it before – Untung Tanujaya Jul 15 '19 at 08:39
@UntungTanujaya you're welcome. Excuse me, also my bad that it's an update statement, and I should explicitly write whole select statement within the `where` clause. – Barbaros Özhan Jul 15 '19 at 09:16

Himanshu · Answer 2 · 2019-07-15T09:12:42.053

The below query does check the invoices or customers for duplicates done via group by. Grouping invoices will group the repeated invoices and will give the aggregate count as greater than 1 if found repeated else 0 similarly in or condition for customers. Is this what you want ?

  Update Table 
  Set Product="Product C" where
   Invoice In
   (Select  Invoice
   from table group by
    Invoice having 
    count(*)>1) or 
     Customers In (Select  Customers
   from table group by
    Customers having 
    count(*)>1)

score 0 · Answer 3 · answered Jul 15 '19 at 10:17

If you just want a query which returns the requested information (so no update):

select distinct CASE WHEN (select count() 
                             from Tab t2 
                            where t2.Customer = t1.Customer or t2.Invoice = t1.Invoice) > 1 
                     THEN 'Product C' 
                     ELSE ProductGroup 
                END "ProductGroup", Invoice, Customer
from Tab t1

change and merge multiple values on column if there is duplicate value in other columns

3 Answers3