SQL Server: Read only views via GROUP BY and UPDATE FROM pitfalls #sql #sqlserver

I often hit a pitfall when doing an UPDATE FROM when it is from a table INNER JOINed with itself but I only want to update one side of the join. However SQL Server would complain that it doesn’t know which Table_1 is to be considered anyway. Why would I be doing this? Imagine a table with a parent child relationship, so an id column and a parent id column that contains the id of the parent record. If I wanted to update children based on some criteria of the parent I would look at doing an UPDATE FROM with the table inner joining to itself on id equals parent id with the where criteria I wanted.

I get around this by using views, but it can be tricky to make sure I only update the child records and not the parent records, so I employ a mix of table and view. Unfortunately there is no such thing as a read only query or a read only view, but you can do a few tricks to make them non-updatable. For a view a GROUP BY (or DISTINCT) is one way and that is what I’ll show too.

Here are a couple of examples of things that work and things that don’t, unfortunately I came up with the example before this article, so it isn’t specific to a parent child relationship, but still a good example:

CREATE TABLE [dbo].[Table_1](
	[id] [int] NULL,
	[val] [int] NULL
) ON [PRIMARY]
GO

-- Three rows.
INSERT INTO Table_1 (id, val) VALUES (1, 5)
INSERT INTO Table_1 (id, val) VALUES (2, 10)
INSERT INTO Table_1 (id, val) VALUES (3, 15)
GO

CREATE VIEW View_2
AS
SELECT id, val FROM Table_1 GROUP BY id, val
GO

-- Fails, because of GROUP BY, effectively the view is read only, without the group by this would work.
UPDATE View_2
SET val = 1

-- Fails, which Table_1 is to be updated? It doesn't make sense does it?
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN Table_1 t2
ON t1.Id != t2.Id
WHERE t1.id = 1

-- Only 1 row affected, View_2 is not updated but NOT because of GROUP BY, it is not updated because we are updating Table_1
-- The query actually returns two rows (or all three if you see what I mean), try it for yourself.
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN View_2
ON t1.Id != View_2.Id
WHERE t1.id = 1

-- The exception to this is if Table_1 isn't in the FROM part. So the following statement updates all rows even though you might think View_2 should make this fail; nasty!
UPDATE Table_1
SET val = 1
FROM View_2

An alternative to a view is to use the WITH statement, like so:

WITH v AS (SELECT id FROM Table_1)
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN v
ON t1.Id != v.Id
WHERE t1.id = 1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s