Skip to content

Instantly share code, notes, and snippets.

@arjunkori
Last active November 29, 2018 06:11
Show Gist options
  • Save arjunkori/ed148441f57e632d6f25d2efd868273c to your computer and use it in GitHub Desktop.
Save arjunkori/ed148441f57e632d6f25d2efd868273c to your computer and use it in GitHub Desktop.
MYSQL QUERIES
/*
* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment