UPDATE rows with values from a table JOIN in Oracle

Example use case: I have a database that contains a table of contacts (contact) and table of e-mail addresses (email), joined on contact.id = email.contact_id. I just found out that Example Conglomerate acquired Osric Publishing’s Oracle consulting business, and so I need to update my contacts database so that all of the Oracle consultants who had @osric.com e-mail addresses now have @example.com e-mail addresses.

How can I change just the affected addresses in the contact database, assuming the username portion of their e-mail addresses remains the same?
Continue reading UPDATE rows with values from a table JOIN in Oracle

Selecting the newest row in one-to-many on mySQL

This one was quite a sticky wicket. So what if you have two tables in mySQL, with a one-to-many relationship (say a forum and comments in that forum), and each of the comments is dated. Now let’s say you want a result set containing each forum’s name, and with it the text of the newest comment in that forum.

The obvious way to do this is with a subselect, which mySQL doesn’t have. So how does one do it?

At first I thought that it might be impossible, but I have figured out the answer:

SELECT f.forum_id,
       max(c.created_on) as last_date,
       c2.created_on as created_on,
FROM   forum f
       LEFT JOIN comment c ON (c.forum_id = f.forum_id)
       LEFT JOIN comment c2 ON (f.forum_id = c2.forum_id) 
GROUP BY f.forum_id, c2.comment_id
HAVING created_on = last_date OR last_date IS NULL;

See, it’s pretty clever actually, though it leans on the “HAVING” option, which is sort of crappy. The trick is to join the comment table with the forum table twice. One of those joins gets grouped by forum id so that you can use the max function on it. The other doesn’t get grouped (by adding its primary key to the group by clause) so that you can still pull data out of it. Then you use the having clause to find only the row that has the max date.

I am also accepting values with a max value of NULL so that if a forum has no comments, it still comes back in the results.

Hope this helps someone, somewhere.
Happy Hacking

Checking for date-time conflicts

It seems like I’ve worked on a lot of applications lately that involve time slots: room reservation systems, appointment schedulers, and so on. One thing that tripped me (and some other developers) up at first was checking for conflicting time slots. It turns out, there are a variety of possible conflicts:

Conflicting Time Slots

There are 5 unique conflicts possible, and it seems like I’m always forgetting one.

Continue reading Checking for date-time conflicts