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

Right now governments really should have a deficit
Right now governments really should have a deficit
The argument for AV
The argument for AV
'Too big to fail' vs 'breaking up the banks'
'Too big to fail' vs 'breaking up the banks'
The 12 day strike against BA
The 12 day strike against BA
Essential lesson in coding better Javascript
We should tax the bankers to underwrite our risk
We should tax the bankers to underwrite our risk
RBS and Bankers' Bonuses
Giving up on SOAP for good
Giving up on SOAP for good
TEE09: Friday 13th November
TEE09: Friday 13th November
TEE09: Thursday 12th November
TEE09: Wednesday 11th November
TEE09: Tuesday 10th November
TEE09: Tuesday 10th November
TEE09: Monday 9th November
In scapegoating sub-prime lending the FSA is missing the point
In scapegoating sub-prime lending the FSA is missing the point
Fumblerules
Fumblerules
Why do we still have IE6 and what are Google doing about it?
Why do we still have IE6 and what are Google doing about it?
5
Introduction to MDX
Introduction to MDX
A tale of two jobs, or when does money stop mattering?
A tale of two jobs, or when does money stop mattering?
How to adapt agile to different companies? My MBA thesis
How to adapt agile to different companies? My MBA thesis
Sorry, long time no posts
Sorry, long time no posts
Fall of the fat cats?
Fall of the fat cats?
Choosing a thesis topic II
Choosing a thesis topic II
2
The killer Word 2007 feature
The killer Word 2007 feature
Choosing a thesis topic I
Choosing a thesis topic I
stackoverflow.com
stackoverflow.com
Getting generic forms to work in C#
Getting generic forms to work in C#
Redesigning the Xbox dashboard
Redesigning the Xbox dashboard
The State of the Web - Summer 2008
The State of the Web - Summer 2008
The Black Swan and the LHC
Leaders, managers and bosses
2
When left and right just don't cut it
When left and right just don't cut it
Neat tricks on Gmail
Neat tricks on Gmail
Office war!
Office war!
Hype versus Craft
Hype versus Craft
1
Sparklines - a useful tool for showing data inline
Sparklines - a useful tool for showing data inline
Labels don't help
Labels don't help
Radiohead's Nude remixed on old kit
Radiohead's Nude remixed on old kit
1
Kindle a success, but not over here
Kindle a success, but not over here
Simple text and accessibility
Simple text and accessibility
C# value types and equality
C# value types and equality
Further pitfalls of GPL
Further pitfalls of GPL
Useful application for demos
Useful application for demos
Why seek a better way?
Why seek a better way?
2
When not to listen to users
When not to listen to users
What happened to grammar?
What happened to grammar?
Learning from management stress
Learning from management stress
The future has less commuting
The future has less commuting
Corporate branding vs software and web applications
Corporate branding vs software and web applications
2
Floating point numbers - more inaccurate than you think
Floating point numbers - more inaccurate than you think
I don't know how long anything will take
I don't know how long anything will take
A commonly repeated misunderstanding
A commonly repeated misunderstanding
The annoying fundamental flaw with SQL
The annoying fundamental flaw with SQL
Creepy, but impressive
Creepy, but impressive
Are you overpaying your CEO?
Are you overpaying your CEO?
Plan Do Check Act
We do agile, but with a small "a"
We do agile, but with a small "a"
Continuous vs iterative Agile
Continuous vs iterative Agile
無駄 in software development
無駄 in software development
The arrogance of developers
The arrogance of developers
Theory X & Y of Agile
Theory X & Y of Agile
4
Systematic misunderstanding of the word "methodology"
Systematic misunderstanding of the word "methodology"
2
W. E. Deming: true grandfather of Agile?
W. E. Deming: true grandfather of Agile?
Test Driven Development: the 3 laws
Test Driven Development: the 3 laws
Why YAGNI doesn't work
Why YAGNI doesn't work
Listen to users & don't listen to users
Listen to users & don't listen to users
1
改善 optimises, but can it innovate?
改善 optimises, but can it innovate?
Something fundamentally wrong with management
Something fundamentally wrong with management
Expensive database server = 1980s Filofax
Expensive database server = 1980s Filofax
EFQM - a red food diet?
EFQM - a red food diet?
When market forces don't work
When market forces don't work
Computers didn't learn rounding at school
Computers didn't learn rounding at school
Why hardly anyone cares about Freedom Zero
Why hardly anyone cares about Freedom Zero
Transformational Vs Transactional Leadership
Transformational Vs Transactional Leadership
Blu-Ray Vs HD-DVD: the winner is IPTV
Blu-Ray Vs HD-DVD: the winner is IPTV
Equal pay
Equal pay
Halo 3: Hardcore gaming & social content
Halo 3: Hardcore gaming & social content
The extreme male brain type
The extreme male brain type
Seam carving image resizing
Seam carving image resizing
1
Just get it working Vs Quality
Just get it working Vs Quality
Bioshock, and development compromises
Content is not king
Content is not king
Job Evaluation 101
Calculating product cost
Modern scientific management
Modern scientific management
Finally, a map of all the online communities
Finally, a map of all the online communities
Web 2.0 vs the original WWW
Web 2.0 vs the original WWW
Trend from Taylorism to employee participation
Quality vs Innovation
Quality vs Innovation
Friends Reunited (.Com) Vs Facebook (Web 2.0)
Friends Reunited (.Com) Vs Facebook (Web 2.0)
2
McJobs, last bastion of Scientific Management?
McJobs, last bastion of Scientific Management?
How business works
How business works
Adventures in Agile Part I
Adventures in Agile Part I
One Laptop Per Child
One Laptop Per Child
1
The new web advertising model, and how to work it
The new web advertising model, and how to work it
3
Web 2.0 = Bubble 2.0
Web 2.0 = Bubble 2.0
4
HRM and Organisational Strategy
Basic introduction to what programmers do
Basic introduction to what programmers do
What's gone wrong with the PS3?
What's gone wrong with the PS3?
Software Development Process Vs Experiential Learning Cycle
Label Cloud
Blog Archive
About Me
About Me
Blogroll
Blogroll
Loading