domingo, diciembre 07, 2008

Firebird 2.1 UPDATE OR INSERT

Another great feature that I like in Firebird 2.1 is the UPDATE OR INSERT statement. It's a really time saver and it makes the SQL cleaner.

For example suppose I have a products table like the one I use in my last post and an inventory table to store the product stock. Before Firebird 2.1 if I want to set the stock for a product I needed to check if a record for that product_id already exists; if the product_id already exists then I write an update. If not then I write an insert statement. So I ended up with something like this:


IF EXISTS(SELECT * FROM inventory WHERE product_id = :product_id ) THEN
UPDATE
inventory
SET
stock = :stock
WHERE
product_id = :product_id;
ELSE
INSERT INTO inventory
(product_id, stock)
VALUES
(:product_id, :stock);

In this example I only update one field but when I have to update a big table I ended up with a big chunk of code and thinking: "there should be another (better) way to do this".

Fortunately now with Firebird 2.1 there is a better way to do it, I only need to use the UPDATE OR INSERT statement and the engine will take care of checking if the record already exists, based on the primary key value. If the table does not have a primary key defined then I should use the MATCHING keyword with the columns I want to match. So in this example the sql can look like this:

--product_id is the primary key

UPDATE OR INSERT INTO inventory
(product_id, stock)
VALUES
(:product_id, :stock);

or like this

--product_id is not defined as the primary key

UPDATE OR INSERT INTO inventory
(product_id, stock)
VALUES
(:product_id, :stock)
MATCHING
(product_id);


This code looks much cleaner and does not need any comments to explain what is going on.

I really like this new feature.

4 comentarios:

  1. Hi,

    Your first code could be cleaner. Instead of doing a SELECT to check the existence of the record, do an UPDATE, and ROWCOUNT is zero, do an INSERT.

    Thanks for the article.

    ResponderEliminar
  2. i think MERGE statments are the new ANSI SQL specifiation for this same purpose. Is it a deviation from standards?

    ResponderEliminar
  3. @Jobin Yes you can use the MERGE statement in Firebird for that, but in this example (of just one row) I think it's easier with the UPDATE OR INSERT statement. I use MERGE when I want to insert or update the data from one table to another.

    Using the MERGE statement with this example, the SQL looks like this:

    MERGE INTO inventory i
    USING (SELECT p.product_id FROM products p WHERE p.product_id = :product_id) p
    ON (p.product_id = i.product_id)
    WHEN MATCHED THEN
    UPDATE SET
    i.stock = :stock
    WHEN NOT MATCHED THEN
    INSERT (product_id, stock)
    VALUES (p.product_id, :stock);

    thanks for the feedback

    ResponderEliminar
  4. Muchas gracias Mario.
    Aprendiendo FireBird con C# para crear reportes corporativos con Crystal Reports Usando B.D. Microsip
    Me ayudo mucho la aclaración de Update o Insert,
    Pero lo que aceleró mas el proceso fue el de evitar el Catch de una falla de conversión a número hasta que identifique que era un campo en NULL

    Ismael Jimenez.

    ResponderEliminar