how to combine three columns and remove duplicates in sql-CodePudding

I have table with 7 columns where I want to combine three column and want to remove duplicates. Your help will be appreciated.

TABLE

HIGHLIGHT DUPPLICATE WITH MERGED COLUMN

FINAL RESULT

Col1,Col2,Col3,Col4,Col5,Col6,Col7
1,A,123456,data1,data1,data1,data1
2,B,78910,data2,data2,data2,data2
3,C,45698,data3,data3,data3,data3
1,A,123456,data1,data1,data1,data1
2,B,78910,data2,data2,data2,data2
3,C,45698,data3,data3,data3,data3
4,D,85969,data5,data5,data5,data5

CodePudding user response：

I'm guessing you want to update the table SERIAL_NUMBERS with just a single row of combined warranty_indicator, account, date_sold data. If that's true then I will suggest the following.

Duplicate SERIAL_NUMBERS table:

   CREATE TABLE SERIAL_NUMBERS_NEW LIKE SERIAL_NUMBERS;

Add unique constraint - combination of warranty_indicator, account, date_sold:

   ALTER TABLE SERIAL_NUMBERS_NEW
    ADD CONSTRAINT Index1 UNIQUE (warranty_indicator,account,date_sold);

Insert data from SERIAL_NUMBERS table to SERIAL_NUMBERS_NEW using INSERT IGNORE ; to ignore duplicates:

   INSERT IGNORE INTO SERIAL_NUMBERS_NEW 
    SELECT * FROM SERIAL_NUMBERS;

Rename old and new table:

   RENAME TABLE SERIAL_NUMBERS TO SERIAL_NUMBERS_OLD;
   RENAME TABLE SERIAL_NUMBERS_NEW TO SERIAL_NUMBERS;

Check data:

   SELECT * FROM SERIAL_NUMBERS_OLD;
   SELECT * FROM SERIAL_NUMBERS;

Keep in mind that any future data inserted will treat duplicates according to the unique constraint. Therefore, if you have program running the INSERT syntax, make sure you update it to INSERT IGNORE.

Demo fiddle