Sometimes you will be needing to update the same field in the same table. However, in this case Mysql does not allow to reference the same table in both outer and inner select queries. So, came up with following stepsStep 1: Creating a temporary table which will be deleted later with those rows from main table.CREATE TABLE IF NOT EXISTS tbl_temp SELECT * FROM tbl_main WHERE field_id = 53Step: 2Ref...
In one of my projects, I had to find and delete duplicate rows in parent and child tables. So, I came up with following rough queries and kept here for my future references. SELECT id, NAME, COUNT(role_id) AS numofroles FROM users GROUP BY NAME HAVING numofroles > 2 SELECT * FROM users WHERE NAME LIKE 'DANIEL HEILIG' SELECT COUNT(id) FROM users WHERE NAME LIKE 'DANIEL' SELECT * F...