MYSQL

Note: All these based on MySQL 5.1.61

1. Find duplicate entries in table

SELECT COUNT(*), column1, column2 FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;

1b. List Down all the duplicate entries

SELECT userid, users.user_ic, useremail, company FROM `users` INNER JOIN (SELECT useric FROM users WHERE useric <> “” GROUP BY `useric` HAVING COUNT(*)>1) dup ON users.user_ic = dup.user_ic order by dup.useric

2. Find entries that are null id on the other table

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.ID) WHERE table2.ID IS NULL

3. Find distinct/unique records

SELECT DISTINCT column1, column2 FROM table ORDER BY column1, column2

4. SELECT whether value exists among many possible values

SELECT * FROM table1 WHERE value NOT IN (‘value1’, ‘value2’, …)

4. Joining / combining 2 tables with same structure

SELECT * FROM table1 UNION SELECT * FROM table2
Note: Able to CREATE VIEW using the above syntax to form new VIEW

5. Create & populate table from a VIEW

CREATE TABLE table AS SELECT * FROM view;
INSERT INTO table SELECT * FROM view;

6. Read UTF-8 in MySQL

Put this line before any query:
mysql_query(“SET character_set_results=utf8”, $dbconn); 

7. Create Trigger to update another table in different database

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
FOR EACH ROW
INSERT INTO db2.table2 (col1,col2,datetime,…) VALUES (NEW.col1,NEW.col2,SYSDATE(),…);

Alternatively, for multiple actions, need to add the following items:

DELIMITER |

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
FOR EACH ROW

BEGIN
stmt1;
stmt2;
SET NEW.col3=(SELECT col FROM table1 WHERE col2=NEW.col2);
END;
|

DELIMITER ;

Thanks to John Hundley, Jonathan Haddad, MySQL :)
Read for further readings on Trigger:

  1. Forum1
  2. MySQL Expert
  3. With If Else Condition
  4. More Examples
  5. Interesting Complementary
  6. Default Help
  7. Another interesting read
  8. Very detailed help
  9. Similar reference

8. DELETE records on N join tables

DELETE table1, table2, … FROM table1 JOIN table2 ON () WHERE condition

9. UPDATE COLUMN based on results of SELECT statement

UPDATE table_A (INNER JOIN (SELECT table_A.id as reg_id, table_B.target as pf FROM table_A LEFT JOIN table_B ON (table_A.a_id = table_B.b_id)) as current_select SET table_A.target = current_select.pf WHERE table_A.id = current_select.reg_id

or probably this could work also (haven’t tried)

*UPDATE table_a SET column_a1 = (SELECT column_b1 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3);

*taken from Karlsson blog at: http://karlssonondatabases.blogspot.sg/2009/01/multicolumn-update-with-subquery-mysql.html

10. UPDATE COLUMN with VALUE from Another Table

UPDATE table_b INNER JOIN table_a ON (table_a.id = table_b.id) SET table_b.name = table_a.name WHERE table_a.id > x

11. UPDATE COLUMN replacing PART of String

UPDATE table_A  SET url = REPLACE (url, ‘domain1’, ‘domain2’) WHERE url like ‘%domain1%’

12. GROUP COLUMN & Count

SELECT  columnA AS `Col A`, COUNT( DISTINCT user_id) AS Count FROM A JOIN B ON (A.id = B.id)  WHERE (A.col2 <> “x”) GROUP BY user_type, columnA order by start_date

Leave a Reply