To cause an error when updating a row with non-existing lookup reference

• Download SqlServerCoalesceInUpdate.zip - 627 B

Introduction

The tip shows how to intentionally cause an error in a situation where a non-existing reference would be updated. Even though this tip shows only an example for a lookup reference, the same idea can be used in several different kinds of situations.

Problem description

To understand the solution, lets have a look at the problem. First we need a few tables:

----------------------------------------------------
-- Create tables
----------------------------------------------------
CREATE TABLE LookupData (
   LookupId     INT NOT NULL PRIMARY KEY,
   LookupText   VARCHAR(100)
);
GO

CREATE TABLE FactData (
   FactId        INT NOT NULL,
   FactText      VARCHAR(100),
   FactLookupId  INT FOREIGN KEY REFERENCES LookupData(LookupId)
);
GO

And some data

----------------------------------------------------
-- Insert some data
----------------------------------------------------
INSERT INTO LookupData (LookupId, LookupText) VALUES (1, 'Lookup 1');
INSERT INTO LookupData (LookupId, LookupText) VALUES (2, 'Lookup 2');

INSERT INTO FactData (FactId, FactText) VALUES (1, 'Fact 1');
INSERT INTO FactData (FactId, FactText) VALUES (2, 'Fact 2');

Ok, now consider a situation where you (or the application ) needs to update a row in the FactData table. The FactLookupId needs to be set based on the text in the lookup table. So the UPDATE statement could look like this

----------------------------------------------------
-- Update the fact table with nonexisting lookup text
----------------------------------------------------
UPDATE FactData 
SET FactLookupId = (SELECT ld.LookupId FROM LookupData ld WHERE ld.LookupText = 'Lookup  1')
WHERE FactId = 1;

In the statement above there's a small mistake, there is two spaces in the literal constant 'Lookup  1'. Now when you run the statement the response will be 

(1 row(s) affected)

Just by looking the response, nothing alerts that something is wrong. If you select the data from the FactTable you'll see that even though one row was updated, the value for the FactLookupId is still NULL

----------------------------------------------------
-- Select the data from the fact table 
----------------------------------------------------
SELECT * FROM FactData;

The result

FactId   FactText   FactLookupId
------   --------   ------------
1        Fact 1     NULL
2        Fact 2     NULL

I know you're going to say that you never use the text to fetch the row. The table has a primary key so it would have been used. That's correct but the problem remains. The row containing the Lookup value could have been removed before updating the FactTable.

Consider the following statements

----------------------------------------------------
-- Update the fact table with nonexisting lookup id
----------------------------------------------------
DELETE FROM LookupData WHERE LookupId = 1;

UPDATE FactData 
SET FactLookupId = (SELECT ld.LookupId FROM LookupData ld WHERE ld.LookupId = 1)
WHERE FactId = 1;

Again the same response

(1 row(s) affected)

And still the same situation

----------------------------------------------------
-- Select the data from the fact table 
----------------------------------------------------
SELECT * FROM FactData;

Gives as a result:

FactId   FactText   FactLookupId
------   --------   ------------
1        Fact 1     NULL
2        Fact 2     NULL

When you think about this from the program point of view, the update statement is executed just fine and correct amount of rows has been updated. There's no way of knowing if the result is what you wanted unless you check the result in one way or another

Using COALESCE to generate an error

As said, checking the result can be done in several different ways. In this tip COALESCE is used to generate an error when the lookup value does not exist at the time of update.

Let's jump directly to the statement:

----------------------------------------------------
-- Update the fact table with nonexisting lookup id
-- with COALESCE
----------------------------------------------------
UPDATE FactData 
SET FactLookupId = COALESCE( (SELECT ld.LookupId FROM LookupData ld WHERE ld.LookupId = 1), 0/0)
WHERE FactId = 1;

The statement is almost the same as in the problem description. However, in this statement COALESCE function is used. The inner query still fetches the value from LookupData table and if the row is not found, NULL is returned. But since COALESCE tries to select the first non null value from the list, the next item is selected in such case. Because the next item in the list is 0/0, a division by zero will occur. If you try to run the statement you'll receive

Msg 8134, Level 16, State 1, Line 56
Divide by zero error encountered.
The statement has been terminated.

Now this is easy for the program to notice, something went wrong and nothing was updated.

And if you use the same statement with an existing lookupdata row like this

----------------------------------------------------
-- Update the fact table with existing lookup id
-- with COALESCE
----------------------------------------------------
UPDATE FactData 
SET FactLookupId = COALESCE( (SELECT ld.LookupId FROM LookupData ld WHERE ld.LookupId = 2), 0/0)
WHERE FactId = 1;

One row gets updated and the data looks like this

----------------------------------------------------
-- Select the data from the fact table 
----------------------------------------------------
SELECT * FROM FactData;

Gives (at last)

FactId   FactText   FactLookupId
------   --------   ------------
1        Fact 1     2
2        Fact 2     NULL

That's it for this tip. And as said in the beginning, this is just one scenario where this kind of idea can be used.

History

• 10^th September, 2014: Created