Duplicate records in mysql

Below is the query which can be used to find the duplicate records in a mysql table. Also can be used to retrieve the count of records matching certail condition.

select FIELD1,count(*) count FROM TABLE1 t1
join TABLE2 t2 on t2.ID=t1.EID where t1.EID in (200,300)
and t2.relation in ('FATHER','MOTHER','SISTER')
group by FIELD1
having count(*) > 0;

UPDATE CASE IN MYSQL

Below is the code which demonstrates the usage of CASE statement for UPDATE in MYSQL

UPDATE employee SET
empgrade=
  CASE 
    WHEN UPPER(TRIM(`COL1`))=UPPER('A') THEN A1
    WHEN UPPER(TRIM(`COL1`))=UPPER('B') THEN B1
    WHEN UPPER(TRIM(`COL1`))=UPPER('C') THEN C1
    WHEN UPPER(TRIM(`COL1`))=UPPER('D') THEN D1
    WHEN UPPER(TRIM(`COL1`))=UPPER('E') THEN E1
    WHEN UPPER(TRIM(`COL1`))=UPPER('F') THEN F1
    WHEN UPPER(TRIM(`COL1`))=UPPER('G') THEN G1
    WHEN UPPER(TRIM(`COL1`))=UPPER('H') THEN H1
    WHEN UPPER(TRIM(`COL1`))=UPPER('I') THEN I1
    WHEN UPPER(TRIM(`COL1`))=UPPER('J') THEN J1
    WHEN UPPER(TRIM(`COL1`))=UPPER('K') THEN K1
  END
 WHERE empdept=3;

MySql Random password Generator

Below are the MySql functions which can be used to generate random passwords by including in the mysql query

First Method:

insert into employee (name,dob,userid,password) values ('Harryporter','2002-10-03','harry',substring(MD5(RAND()), -8));


Second Method:

insert into employee (name,dob,userid,password) values ('Harryporter','2002-10-03','harry',CONV(FLOOR(RAND() * 99999999999999), 10, 36));

Import data from one table to other in MYSQL

Below is the MYSQL query which can be used to import bulk data from one table to other table.
The code below also shows the implementation of CASE statement in MYSQL queries

QUERY:

INSERT INTO TABLE1
(
 id,empname,emppwd,empsal,empstatus
)
SELECT id,ename,epwd,

  CASE UPPER(TRIM(level))
    WHEN 'LEVEL I' THEN 150000
    WHEN 'LEVEL II' THEN 200000
    WHEN 'LEVEL III' THEN 300000
  END,
  1
  FROM TABLE2;