MySQL UPDATE with SELECT on the same table in the same query

I’ve been having a week where there’s a ton of stuff to be done in a short time, yet everything seems to take much longer than it should and every task seems to present multiple unforseen obstacles. 

Yesterday, for example, I was working on something that had already taken several days of my time when I’d hoped to get it done in hours. The final straw came when I found myself facing what appeared to be an intractable problem: a process that needed to insert multiple rows in a table, and increment a certain column in each row to get a unique value. I couldn’t use an auto-increment value in this case (there was one already), and using a separate query caused concurrency problems (resulting in non-unique values). I should just point out that this is a system that I’ve inherited, not one that I developed myself.

I started out with a simple update, like this:

UPDATE my_table SET some_value = ( MAX( some_value ) + 1 ) WHERE id = 123;

…which in hindsight was never going to work but anyway, it gave me this error:

#1111 - Invalid use of group function

So I thought about it a bit and then struck on this:

 UPDATE my_table SET some_value =
(
(
SELECT MAX( some_value )
FROM my_table
) +1
)
WHERE id = 123
…which gave me this error:

#1093 - You can't specify target table 'my_table' for update in FROM clause

…damn. I was starting to bang my head on the desk at this point, but Google came to my rescue by pointing me at this thread, which suggested using a nested sub-select. The result was the following rather ugly query:

UPDATE my_table
SET
my_value =
(
(
SELECT selected_value
FROM
(
SELECT MAX( my_value ) AS selected_value
FROM my_table
)
AS sub_selected_value
)
+ 1
)
WHERE id = 123

…which works. It’s not great  from a performance point of view since it uses a temporary table, but it does what it needs to. I hope in future that I will be given time to go back and rewrite this part of the system so as to entirely remove the issue.

Category: Uncategorized | Tags: | 8 Comments »

Enhanced (‘encouraged’) blog commenting

Here’s a great blog post discussing a technique to simplify and encourage commenting on a blog, in particular by quoting text snippets. I might well try to find the time to implement it here on StickBlog.

Category: Uncategorized | Tags: | 1 Comment »

Comparing changes to MySQL databases

One of my personal nightmares goes as follows: I’m working on an existing MySQL-based system, adding or changing something that involves altering existing database tables. I do what I need to, commit the code and then realise that I need to make the database changes too…only I’ve forgotten to keep track of the edits I’ve made. Oops.

Now there are various things I should do to prevent this happening — at the very least keep a list of changes as I’m going along, which I do…when I remember to. But on those occasions when I don’t, I usually end up dumping the schemas and comparing them using a diff tool, which works just fine but can be rather laborious.

Today I stumbled (via DZone) upon a piece of freeware called Toad for MySQL. It does a whole bunch of useful things, but the feature that stood out for me was its ‘schema compare’ tool. At first it looks like a dolled-up diff tool, but then you notice that in addition to showing you the differences between two databases, it also creates an SQL statement to convert between the two. Very handy.

For those of you who are allergic to MySQL, there are versions of Toad for Oracle, SQL Server and DB2 (although they’re not freeware).

Category: Web Development | Tags: | 1 Comment »

jQuery: Dropping items from a draggable list to a sortable list

For reasons that I might outline in another post sometime, I recently decided to replace MooTools as the JavaScript library behind our company CMS (which I’m in the process of rewriting from scratch) with jQuery.

It’s been a few weeks now since I made the decision and I’m starting to become more comfortable with jQuery’s quite different approach. I’ve also begun working on some of the more complicated aspects of the new CMS, including adopting jQuery UI to provide the interface widgets. One requirement of the system is to have a dialog where search results can be dragged and dropped onto a sortable list.

At first I looked at using two sortable lists and the built-in ‘connectWith’ option, which enables you to link two or more sortable lists together. But then I realised that I didn’t really want the search results to be a sortable list. In fact, what I wanted was a static list whose items could be dragged and placed in the sortable list.

I tried a few experiments, and when those failed I decided to try looking at the code itself to see if I could hack together a solution without too much extra effort. While browsing the ui.draggables code, I stumbled across the intriguingly-named ‘connectToSortable’ option. I was surprised to find it because there’s (currently) no mention of it in the documentation, and indeed it’s very hard to find any reference to it at all beyond the code itself (the only useful link I could find was this one, but it rather over-complicates the issue).

It’s very simple to use: as with connectWith, just specify which sortable(s) you want to connect to with the connectToSortable property:

$('#myDraggable › li').draggable({helper:'clone',connectToSortable:'#mySortable'});

Full example code here.

The code for the example works just fine, but I found when using it in my own project, for some reason it was necessary to add a $(‘mySortable’).sortable(‘refresh’) call after defining the two lists, or the first drop operation would always fail.

By the way, when fiddling around with JavaScript experiments I find JSBin very useful — you can load up any of several JS libraries (jQuery, MooTools, dojo, prototype, YUI, script.aculo.us) and create both JavaScript and HTML to test in a ‘live’ environment. Very handy.

Category: Javascript | Tags: | 14 Comments »

Dynamic form elements and Internet Explorer 6

A few brief entries from the ‘tiny yet annoying bugs that take far too much time to fix’ file…

I’m creating forms dynamically using JavaScript. This has led to a number of problems with IE6:

Dynamic checkboxes/radio buttons and the ‘checked’ attribute

When creating a field, I wanted to be able to apply a ‘default’ value — for example, a checkbox element might be ticked by default. This was all going smoothly until (guess what?) I tested it on IE6: the checked attribute was set, and returned ‘true’ when tested, but the box did not appear to be checked when it appeared in the form.

The answer, as I discovered thanks to this forum post, was to use the ‘defaultChecked’ attribute instead (I set both, just in case). It seems to work across browsers, which is nice. Oh, and this applies to radio buttons as well as checkboxes.

Creating radio buttons

While we’re on the subject of radio buttons, I also found that IE6 doesn’t like radio buttons created using document.createElement() (i.e. as a DOM object). It’ll render them OK, but they’re unclickable. The answer is to create them by injecting HTML into an element with [element].innerHTML:

var obj = document.createElement( ‘span’ );
obj.innerHTML = ‘<input type=”radio” name=”somefield” value=1>1′;

…and so forth. Ugly.

Assigning values to multiple select elements

This was a really fiddly one, and seems to happen under only very particular circumstances. Anyway…

If you create a select element and set the ‘multiple’ attribute, IE6 can sometimes have trouble assigning values to it if you do it immediately after it’s created. Note the word ‘sometimes’: I haven’t been able to create a simple enough test case to isolate the exact circumstances that are required. And it’s only a problem if you’re setting more than one value.

Anyway, the (horrible) fix is to delay setting the values using setTimeout(). The delay doesn’t matter (1 millisecond will do), but it does work.

I hope these notes help some other unfortunate soul out there avoid wasting the number of hours and brain cells that I just have!

Category: Javascript | Tags: | 2 Comments »