MySQL Useful Queries

Copy Headings from one table into other table:

create table test2

like table1;

Insert data from one table into other table:

insert into table2

select * from table1

Find duplicate records in a table:

SELECT *,
         ROW_NUMBER() OVER(
             PARTITION BY emp_id, name, age, doj, dob
 ) AS row_num
FROM EmployeeTable;

Note: Write all columns name in the table.
This new row_num is not part of the table.

Delete duplicate records in a table:

To delete duplicate records we can use a CTE (Common Table Expression) with the ROW_NUMBER() function.

Alter table layoffs_staging_2

add row_num int;

-----------------------------------------------------------------------------------------------------------------------------

With CTE as (

SELECT *,

       ROW_NUMBER() OVER(

           PARTITION BY company, location, industry, total_laid_off, percentage_laid_off, 'date', stage, country, funds_raised_millions

       ) AS row_num2

FROM layoffs_staging_2

)

delete from layoffs_staging_2

where row_num IN (select row_num from CTE where row_num2>1);