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:
- Forum1
- MySQL Expert
- With If Else Condition
- More Examples
- Interesting Complementary
- Default Help
- Another interesting read
- Very detailed help
- 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