I've written a lot of SQL. Mostly Microsoft's T-SQL variant. I was a DBA in a high pressure environment looking after over 80 business critical server instances.

It's a language that I'm very confident in, and one that I think is very well suited to the job. However there's one major flaw with how it works that annoys me every time I use it:

SQL has two methods for getting data into a table, depending on whether you're adding or changing data.

--Add
    insert into tablename ( idfield, field1, field2, ... )
    values ( 7, 'value one', 'another value', ... )

--Change
    update tablename 
    set field1 = 'new value',
        field2 = 'different value',
        ...
    where idfield = 7

There are two problems here, the first is that you need to know whether you're adding or changing before you write the SQL. The problem is that you often don't. You can get around this with an additional bit of logic:

--check whether to insert or update
if not exists(
    select * from tablename where idfield = 7 ) 
    insert into tablename ( idfield, field1, field2, ... )
    values ( 7, 'value one', 'another value', ... )
else
    update tablename 
    set field1 = 'new value',
        field2 = 'different value',
        ...
    where idfield = 7

Hardly ideal, it does two I/O operations where only one might be required for starters, but that isn't my issue with it. The real problem is the insert statement - it depends on the order of fields. For two or three fields this isn't a problem, but for more than that it quickly gets to be a royal pain.

Oracle has had, for a while now, a solution for the first problem that nicely fixes the second:

    upsert tablename 
    set field1 = 'new value',
        field2 = 'different value',
        ...
    where idfield = 7

This upsert statement does both update and insert in Oracle, but it also has the huge advantage that you can have one statement that lists fields next to the values you're setting for them. Order doesn't matter.

Now in SQL 2008 Microsoft have 'fixed' this (only they haven't) with the merge statement:

merge into tablename 
where  idfield = 7
when matched then
    update
    set field1 = 'new value',
        field2 = 'different value',
        ...
when not matched then
    insert ( idfield, field1, field2, ... )
    values ( 7, 'value one', 'another value', ... )

The two I/O operations where one was needed problem has been fixed, but the language issue of lots of inserted fields is still there.

Upsert isn't in the SQL:92 standard, and merge is new to the SQL:2003 standard. It's great that Microsoft have caught up with a five year old standard, but I still prefer the Oracle way of doing it.

It's just about the only thing Oracle does better than Microsoft.

0

Add a comment

And I'll tell you why...

The allegory constantly used by governments and the media is on of a household, and the recession is like the breadwinners losing their jobs. Obviously anyone sensible would cut back, yeah?

But, countries are nothing like households, and that allegory just doesn't work.
The UK will vote on May 5th on whether to switch to the Alternative Vote system (AV) rather than the current First Past The Post (FPTP).
There is a problem in UK banking that's stalling our recovery and it goes something like this:

Banks have two main components: high street lending & saving, and investment banking. The investment banking side took far too many risks in dodgy sub prime investments and lost all the money.
British Airways have the best paid flight staff in the world.
I think the current situation with bankers' bonuses shows a complete failure to understand risk and how markets actually work.

There's a TV show in the UK called Property Ladder.
When .Net originally launched it came with first rate support for Simple Object Access Protocol (SOAP) and at the time I was seriously impressed.
Sightseeing

By this point we were starting to feel the tech-fatigue.
DEV317 Agile Patterns: Agile Estimations

Stephen Forte (from Telerik) I quite enjoyed this - it was all honestly and well put. His explanations about the cone of uncertainty were good and overall I felt this was a good presentation.
The FSA announced new regulations today - they're going to be tougher on sub-prime lending. The inference is that it's the sub-prime lending that caused the current crisis.

I think that's only a partial truth.

There's nothing fundamentally wrong with sub-prime lending.
Remember to never split an infinitive. A preposition is something never to end a sentence with. The passive voice should never be used. Avoid run-on sentences they are hard to read. Use the semicolon properly, always use it where it is appropriate; and never where it isn't.
Back in the early 00s Internet Explorer 5 was great. I wrote some web applications (which I was really proud of) that used all sorts of then cutting edge stuff like the Microsoft.XmlHttp ActiveX object to load additional data (a method now called Ajax and used everywhere).
This content is originally from December 2003. It is an abridged version of an article I sold to Sql Server Central. I was looking for it recently and it took me ages to find it, so I've reproduced relevant parts here.
Suppose that you have two jobs – both are professional jobs for which you are qualified and you have a free choice of either one:

Job A: 3 miles from where you live Lovely, well appointed and air conditioned office Friendly co-workers that you enjoy working with 35 hour week with flexible hours and
My master's thesis is to look at how to apply agile.

There is an awful lot of corporate selling of agile - lots of management consultants selling their brand as 'best'.

I'm not interested whether XP, Scrum, Crystal Clear, Agile-CMMI, Six Sigma or any other brand/variant is best.
No posts for a while, sorry about that. It turns out that I have to be very careful on what I post here as it counts as prior publication for my MBA (and therefore inadmissible).

However I'm going to try and get some community feedback on my conclusions, so they'll be up here soon.
Back in March I asked Are you overpaying your CEO? It seems rather prescient looking at the news today.

The BBC is reporting that Bank shares fall despite bail-out - which is kinda stating the obvious. The government's taken majority stakes - they're basically angel venture capitalists.
Following on from part I I have a few more idea:

Idea 4: Deming's theorys were developed for manufacturing, but have also been applied to software development as the "Agile method".

Investigate using this high particiapation model in other industries.
For me Word 2007 has one really 'killer' feature. I didn't think there have been any significant improvements to it since Office 97 - it just gets more expensive and needs more processing with each new version. Now, in Word 2007, they've finally added something that really makes it worth upgrading.
At the moment I'm trying to choose a suitable topic to study for my final MBA year.
I have of late, but how I know not, become rather addicted to stackoverflow.com:

It's currently only in private beta, open to about 2000 users, but I can see it taking off when it goes fully live.
Recently I needed a form, loaded by reflection, that dealt with a collection of some type. I figured that we need to know the type when we loaded the form, so it would be better to have a generic form (save it having to check the object type through reflection for every action).
The Xbox 360's dashboard was quite impressive when the console launched, mainly because it had one at all. However it's not a particularly well designed interface. It works, but it's slow, clunky and wastes a lot of space. There's been rumours of a redesign for a while now.
Nice little graphic on the current state of the web.
Rather worryingly I've found myself supporting David Davis recently. I would have put the guy as far away from me politically as it's possible to get, yet he's making a stand for something I believe in.
Google's excellent Gmail has been my e-mail of choice for a while - despite the rename to googlemail in the UK. It has by far the best web interface.

Recently I got an email address to First.Surname@domain, which is odd because my e-mail is actually FirstSurname (no ".").
On a related end-of-Friday note:

http://view.break.com/521743 - Watch more free videos
There has always been a clash between those that make a product, and those that sell it.

Software engineers are pessimistic, negative and cynical. All engineers have to be. I don't mean that they have negative personalities as such - they just need to constantly worry about what might go wrong.
Label Cloud
Blog Archive
About Me
About Me
Blogroll
Blogroll
Loading