Otaqui.Com

MySQL’s documentation is OK, but it’s examples are sometimes quite poor.

I have a particular setup where values across two tables – a course table and a schedule table which relates to it – can be effectively “overridden”. The idea is that for any given course, one can set “default” values, and then these can be overridden every time a course is actually scheduled to happen.

The courses table looks something like this:

COURSES
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
title VARCHAR(255) NOT NULL
subtitle VARCHAR(255) NOT NULL
description TEXT NOT NULL

SCHEDULES
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
course_id INT(11) NOT NULL
subtitle VARCHAR(255) NULL
description TEXT NULL

Hopefully you can see what I would want during a SELECT – to get the values from the schedules table if they aren’t null, or otherwise get the value from the courses table if they are.

This is achieved with the IFNULL built in function in MySQL. IFNULL takes two arguments, and returns the first argument if it (the first argument that is) is not null, or the second argument if it is. The actual SQL for my example looks like this:

SELECT courses.title, IFNULL(schedules.subtitle,courses.subtitle) AS subtitle, IFNULL(schedules.description,courses.description) AS description FROM schedules LEFT JOIN courses ON schedule.course_id=course.id;

This will give you the “subtitle” and “description” values from the linked table (schedules) if they exist, or the default values from the original table (courses) if they don’t.

I had the need to copy a load of data from one table to another while incrementing (or just adding an arbitrary number to) some numeric indices.

Before I lept to a programming language like PHP to do a load of selects, manipulate the data and then run inserts I thought I’d try and do it in pure SQL. It turns out this is fairly straightforward.

Let’s assume we have two databases, db_source and db_target and that we want to copy stuff from the former into the latter. Both of these have identical tables called ‘users’ and ‘posts’ like so:

USERS
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL

POSTS
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INT(11) NOT NULL,
title VARCHAR(255),
post TEXT

Now, let’s assume that in db_target we *already* have some posts and some users, and what we really want to do is merge the records from db_source with the ones that already in db_target, obviously maintaining all the related keys.

In this example we will say that we have 5 current users in db_target, and 100 posts; and also that the auto_increment values are 1 to 1 in both tables (that is the next auto_increment value will be 6 and 101 for users and posts respectively in db_target).

With these two commands, which will run SO much faster than some kind of programmatic loop, we can copy all the users and posts at a stroke without losing any associations:

# copy the users, adding 5 to the user id while we’re at it:
INSERT INTO db_target.users ( id, name ) SELECT (id+5) AS id, name FROM db_source.users;

# copy the posts, adding 5 to the user_id and 100 to the post id:
INSERT INTO db_target.posts ( id, user_id, title, post ) SELECT (id+100) AS id, (user_id+5) AS user_id, title, post FROM db_source.posts;

Note that I have selected “as” where I have been using the addition operator. Given that we are also specifying which columns we are updating I’m not 100% sure that it’s necessary, but I find it helpful anyway.