I like SQL, I really do. I like declarative languages in general.

But there is one thing about it which really annoys me. And that is the very different syntax between inserting and updating a record. it isn’t because they are difficult to remember, they aren’t. The standard CRUD (Create/Retrieve/Update/Delete) operations are the type of statements that most SQL users could type in their sleep.

Some queries can get very complex – far beyond the capabilities of my tiny brain. But I can insert and update records.

What I think would be nice would be a single statement that would update or insert a record, depending upon whether it already existed in the database table. An example is probably in order.

Suppose we have a very simple table customer and we want to insert a new row then we would have something like this
Insert into Customer (Surname, FirstName) Values("Smith","Bob")

Now let’s update that row.
Update Customer Set Surname = "Jones", FirstName = "Bill" where CustomerID = 1

Now here is the problem. When I get data from a database I put it into an entity object. I do it using an Object Relational Mapper, but it doesn’t really matter, you could write your own code and build your own classes. Whichever way you do it you are going to eventually call a Save method, or something like it. The O/R mapper I use, and I think most operate in a similar way, knows when it tries to fill the entity whether it is a new entity or not. So, for example, if I write the following code in VB.NET
Dim Customer as new CustomerEntity(1)
or in C#
CustomerEntity Customer = New CustomerEntity(1)
then I get the entity Customer with data from the database for Customer with an ID of 1. Everything is fine. If the Customer does not exist then my Customer has a property IsNew which is set to True.

When I Save this customer the O/R mapper uses the Insert syntax if the Customer is new, or the Update syntax if the customer already exists, and it does this on the basis of the IsNew property.

But I run into problems if I use a collection of customers. When I fill a collection from the database there is no problem, provided I don’t want to save it. The problem arises because the collection can change. In the .NET world a collection implements the IList interface which means that a collection can be added to. So now the collection has no way of knowing whether each of its items already exists in the database. I might have a collection of, say 10 customers, and then I add 3 more. When I save the collection I don’t want to make three trips to the database, I want all three saved in one query. But I can’t do that because there is no way of knowing whether the Save method should call the Insert syntax or the Update syntax.

If there was one syntax used for both then life would be so much easier.

Of course the syntax would have to change a bit. I am thinking about how I would like it to look. More on that later, perhaps.

But anything to save unnecessary round trips to the database has to be a plus.