📖 Posts | 📎 Development | 🔖 mysql, sql

Update one table using data from another

If you want to update tbl1 with information from tbl2: UPDATE tbl1,tbl2 SET tbl1.col1=tbl2.colx WHERE …

📖 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: 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: