SELECT COUNT(*), column1, column2 FROM tablename
\nGROUP BY column1, column2
\nHAVING COUNT(*)>1;<\/p>\n
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<\/span><\/p>\n SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.ID) WHERE table2.ID IS NULL<\/p>\n SELECT DISTINCT<\/em> column1, column2 FROM table ORDER BY column1, column2<\/p>\n SELECT * FROM table1 WHERE value NOT IN (‘value1’, ‘value2’, …)<\/p>\n SELECT * FROM table1 UNION SELECT * FROM table2 CREATE TABLE table AS SELECT * FROM view; Put this line before any query<\/span>: CREATE TRIGGER trigger_name<\/em> BEFORE INSERT ON table_name Alternatively, for multiple actions, need to add the following items:<\/span><\/p>\n DELIMITER |<\/strong><\/p>\n CREATE TRIGGER\u00a0trigger_name<\/em>\u00a0BEFORE INSERT ON\u00a0table_name<\/em> BEGIN<\/strong> DELIMITER ;<\/strong><\/p>\n Thanks to John Hundley,\u00a0Jonathan Haddad, MySQL \ud83d\ude42 DELETE table1, table2, … FROM table1 JOIN table2 ON () WHERE condition<\/em><\/p>\n UPDATE table_A<\/em> (INNER JOIN (SELECT table_A<\/em>.id as reg_id<\/em>, table_B<\/em>.target as pf<\/em> FROM table_A<\/em> LEFT JOIN table_B<\/em> ON (table_A<\/strong>.a_id<\/em> = table_B<\/strong>.b_id)) as current_select<\/em> SET table_A<\/em>.target = current_select<\/em>.pf WHERE table_A<\/em>.id = current_select<\/em>.reg_id<\/p>\n or probably this could work also (haven’t tried)<\/p>\n *UPDATE table_a SET column_a1 = (SELECT column_b1 FROM table_b *taken from Karlsson blog at:\u00a0http:\/\/karlssonondatabases.blogspot.sg\/2009\/01\/multicolumn-update-with-subquery-mysql.html<\/a><\/p>\n UPDATE table_b<\/em> INNER JOIN table_a<\/em> ON (table_a.id = table_b.id<\/em>) SET table_b.name = table_a.name<\/em> WHERE table_a.id > x<\/em><\/p>\n UPDATE\u00a0table_A<\/em>\u00a0\u00a0SET url<\/em>\u00a0= REPLACE (url, ‘domain1’, ‘domain2’)\u00a0<\/em>WHERE url<\/em>\u00a0like ‘%domain1%’<\/em><\/p>\n SELECT \u00a0columnA AS `Col A`, COUNT( DISTINCT user_id) AS Count FROM A JOIN B ON (A.id = B.id) \u00a0WHERE (A.col2 <> “x”) GROUP BY user_type, columnA order by start_date<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":" Note: All these based on MySQL 5.1.61 1.\u00a0Find 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 … Continue reading 2.\u00a0Find entries that are null<\/em>\u00a0id on the other table<\/h2>\n
3.\u00a0Find distinct\/unique records<\/h2>\n
4. SELECT whether value exists among many possible values<\/h2>\n
4.\u00a0Joining \/ combining 2 tables with same structure<\/h2>\n
\nNote: Able to CREATE VIEW using the above syntax to form new VIEW<\/p>\n5. Create & populate table from a VIEW<\/h2>\n
\nINSERT INTO table SELECT * FROM view;<\/p>\n6.\u00a0Read UTF-8 in MySQL<\/h2>\n
\nmysql_query(“SET character_set_results=utf8”, $dbconn);\u00a0<\/span><\/em><\/span><\/p>\n7. Create Trigger to update another table in different database<\/h2>\n
\n<\/em>FOR EACH ROW
\nINSERT INTO db2.table2 (col1,col2,datetime,…) VALUES (NEW.col1,NEW.col2,SYSDATE(),…);<\/p>\n
\nFOR EACH ROW<\/p>\n
\nstmt1;
\nstmt2;
\nSET NEW.col3=(SELECT col FROM table1<\/em> WHERE col2<\/em>=NEW.col2<\/em>);
\nEND;
\n|<\/strong><\/p>\n
\nRead for further readings on Trigger:<\/p>\n\n
8. DELETE records on N join tables<\/h2>\n
9. UPDATE COLUMN based on results of SELECT statement<\/span><\/h5>\n
\nWHERE table_b.column_b3 = table_a.column_a3);<\/p>\n10. UPDATE COLUMN with VALUE from Another Table<\/h2>\n
11. UPDATE COLUMN replacing PART of String<\/h2>\n
12. GROUP COLUMN & Count<\/h2>\n