Wednesday, June 27, 2007

Everybodys Free (to write software) 

I've decided to take the challenge issued by Steve Trefethan, and have extended his excellent list with my take:

(10) comments

Friday, June 01, 2007

Inserting a record only once 

A few weeks back, my boss showed me a technique he uses when inserting a record into a FireBird table, which caters for the scenario that the record may already be there and therefore shouldn't be inserted. It was one of those simple yet elegant solutions, and I've been meaning to share it here. Well, I'm waiting for a data import process to finish, so I thought I'd take the time to finally do this.

The technique revolves around turning the concept of a left join on its head and using it to check for failure (i.e a null field value). Here is an example of what I'm talking about :-

insert into tax_category(category_description, tax_rate)
select 'GST', '12.5' from rdb$database
left join tax_category t
on t.category_description = 'GST'
where t.category_id is null;

The above snippet will attempt to insert a record into the tax_category table using a set of hard-coded values, but because of the left join and where clause, the recordset being used for the insert will be empty if the join finds an existing record in the table.

Labels: , , , , ,

(24) comments

This page is powered by Blogger. Isn't yours?