MYSQL QUERIES

MYSQL RESET AUTO INCREMENT TO 1

/*
* MYSQL RESET AUTO INCREMENT TO 1
*/

SET  @num := 0;
UPDATE your_table SET id = @num := (@num+1);
ALTER TABLE your_table AUTO_INCREMENT =1;

/*
* remove duplicate 
*/
INSERT IGNORE INTO serial_no_non_gsm_sfdc(`id`,`serialnumber`,`name`,`model`,`status`,`statuscode`,`losid`,`timestamp`,`company_name`,`dealer_name`,`dealer_code`,`finance_amt`,`store_location`,`product_name`,`month`,`invoice_date`,`cre_name`,`sm_name`,`source_system`,`createdAt`,`updatedAt`,`webtop_no`,`product_code`)  
SELECT `id`,`serialnumber`,`name`,`model`,`status`,`statuscode`,`losid`,`timestamp`,`company_name`,`dealer_name`,`dealer_code`,`finance_amt`,`store_location`,`product_name`,`month`,`invoice_date`,`cre_name`,`sm_name`,`source_system`,now(),now(),`webtop_no`,`product_code`
FROM `serial_no_non_gsm`;


/*
* update same column of table 
* CONVERTS STRING DATE TO MYSQL DATE FORMAT
*/

UPDATE serial_no_non_gsm a 
SET 
    a.createdAt = STR_TO_DATE(a.timestamp, '%d/%m/%Y'),
    a.updatedAt = STR_TO_DATE(a.timestamp, '%d/%m/%Y')
where
    a.timestamp is not null;