IT Answers Not just another web development blog

Mysql delete all rows and reset autoincrement

delete all the rows of a mysql talble

 

delete from tablename

 

reset the auto increment key

 

ALTER TABLE tablename AUTO_INCREMENT=0

Merging Two Tables in Mysql

Ok, I had two mailing lists I needed to add together (minus duplicates of coarse)

 

first make sure both talbes have the same fields (one had “last_name” and “first_name” fields while the other had just “name”)

 

UPDATE coo_mailing_list SET name = CONCAT(first_name, ‘ ‘, last_name);

 

bam!!! coo_mailing_list has a name field with first and last names in it.

Next merge the two tables into a new table

CREATE TABLE new_mailing_list SELECT DISTINCT * FROM (SELECT name, email FROM mailing_list UNION ALL SELECT name,email FROM coo_mailing_list) sq;

 

add a primary key field and set to auto increment and rename the old and mailing lists  tables and your

your done!