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