SQL


📖 Posts | 📎 Development | 🔖 MySQL, SQL

Update one table using data from another

If you want to update tbl1 with information from tbl2:


📖 Posts | 📎 Development | 🔖 MySQL, SQL

Cannot directly use the IF function on a MySQL query that uses GROUP BY

Cannot directly use the IF function on a query that uses GROUP BY. This tip provides an alternative that is cross-db. It is extracted from: http://surfnet.dl.sourceforge.net/sourceforge/souptonuts/README_mysql.txt SQL Characteristic Functions: Do it without if, case, or GROUP_CONCAT. Yes, there is use for this … if statements sometimes cause problems when used in combination. The simple secret, and it’s also why they work in almost all databases, is the following functions: sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0 Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs(sign(-1) ) = 0 Data for full example: