'SQL' Tag

• Merging tables with INSERT...ON DUPLICATE KEY UPDATE

  February 21, 2013

  Had a case recently where I had to merge data from two identically structured tables containing nearly identical data. "Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one [...]

  posted in MySQL by shlomi

• Hierarchical data in INFORMATION_SCHEMA and derivatives

  January 8, 2013

  Just how often do you encounter hierarchical data? Consider a table with some parent-child relation, like the this classic employee table: CREATE TABLE employee (   employee_id INT UNSIGNED PRIMARY KEY,   employee_name VARCHAR(100),   manager_id INT UNSIGNED,   CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ) engine=innodb ; +-------------+---------------+------------+ | employee_id | employee_name [...]

  posted in MySQL by shlomi

• Pop quiz: funny syntax

  November 5, 2012

  The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript. Can you guess the results of the following statements? Pop quiz 1 SET @x := 7; SELECT ++@x; What is the computation result? What will be the value of @x? Pop quiz 2 SET @ := [...]

  posted in MySQL by shlomi

• Three wishes for a new year

  September 16, 2012

  Another new year by Jewish calendar. What do I wish for the following year? World peace Good health to all Get a decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL. I mean, I like GROUP_CONCAT, and the many hacks it provides: [1], [2], [3], [4], [5], [6], [7], [8], [9], [...]

  posted in MySQL by shlomi

• SQL: selecting top N records per group, another solution

  August 21, 2012

  A while back I presented SQL: selecting top N records per group, a "give me the top 5 countries in each continent" type of query, and which used an external numbers table and a lot of tedious casting. Here's another solution I came up with (*). Still using GROUP_CONCAT (how else?), but no external table [...]

  posted in MySQL by shlomi

• MySQL joins: ON vs. USING vs. Theta-style

  July 11, 2012

  What is the difference between the following three syntaxes? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id The difference is mostly syntactic sugar, but with a couple interesting notes. To put names, the first two are [...]

  posted in MySQL by shlomi

• Auto caching tables

  March 6, 2012

  Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated? Hint: yes. But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have [...]

  posted in MySQL by shlomi

• MySQL/QueryScript use case: DELETE all but top N records per group

  February 9, 2012

  Some administrative tasks can be simplified by using common_schema/QueryScript. I'm collecting a bunch of these for documentation. Here's one for example: The DBA/developer has the task of retaining only top 3 most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th, ... most populated counties in each continent. Is it possible [...]

  posted in MySQL by shlomi

• Syntax of the day: IS TRUE and IS FALSE

  January 26, 2012

  What makes for a true statement? We usually test statements using a WHERE clause: SELECT * FROM world.City WHERE Population > 1000000 The "Population > 1000000" statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF(): SET @val := 7; SELECT IF(@val > 2, [...]

  posted in MySQL by shlomi

• More MySQL foreach()

  December 2, 2011

  In my previous post I've shown several generic use cases for foreach(), a new scripting functionality introduced in common_schema. In this part I present DBA's handy syntax for schema and table operations and maintenance. Confession: while I love INFORMATION_SCHEMA's power, I just hate writing queries against it. It's just so much typing! Just getting the [...]

  posted in Development, MySQL by shlomi