Array Studio Reading List - a list of useful books

MySQL insert a new row or update old one February 16, 2007

Today I stumbled upon a newish MySQL feature that can often be very useful. Since MySQL 4.1 there’s a non-standard feature (i.e. it’s an extension of SQL standard and won’t work on other databases) that lets you insert a new row, but if it happens that a row with same primary/unique key already exists it will just update that row.

Insert query syntax looks like this:

INSERT INTO table (primarykeycol,col1,col2) VALUES (1,2,3) ON DUPLICATE KEY UPDATE col1=0, col2=col2+1

If there is already a row with primarykeycol set to 1 this query is equal to:

UPDATE table SET col1=0, col2=col2+1 WHERE primarykeycol = 1

Ordinarily to achieve the same result you would have to issue an UPDATE query, then check if there were affected rows and if not issue an INSERT query. This way, you can do everything in one step - first try insert and then update if insert fails.

One situation for which this type of syntax is perfect is when you work with daily counters. For example, you might have a table with PostID, Date and Count columns. Each day you’d have to check if you already created an entry for that day and if so increase the count column - and this can be easily substituted with one INSERT … ON DUPLICATE KEY UPDATE query.

Unfortunately there are some caveats. One being that when you have multiple unique indexes it will act as if you had an OR condition in WHERE clause of UPDATE query. This means that multiple rows should be update, but INSERT … ON DUPLICATE KEY UPDATE will update only one row.

For more information you should read article in MySQL manual: INSERT … ON DUPLICATE KEY UPDATE Syntax

Free Web based color scheme creator February 3, 2007

I simply enjoy this kind of stuff. Specially if client doesn’t have any specific color scheme request. This color cycle is easy to use and you can generate great color schemes, if you know where to click.

First, click on the buttons below the cycle and choose from (Mono, Contrast, Triad, Tetrad and Analogic). Then, play around with colors and see what you’ll get. Use variations to set the colors perfectly. There is also a simple preview where you’ll be able to see how colors fit. I prefer dark and light pastels myself.

Here is the link to Color Scheme Creator. Enjoy.