Thursday, February 28, 2008

SQL Update From Same Table

I recently had a situation where I had to update some columns in several rows of a table with values taken from other rows in the same table. I thought this was the perfect situation to use the SQL "UPDATE ... FROM" strategy, but I ran into some problems.

Here's the set up:

CREATE TABLE table1 (x int not null, mod10 int null )
-- insert one batch of data
INSERT INTO table1 (x, mod10) VALUES (1, 1)
INSERT INTO table1 (x, mod10) VALUES (2, 0)
INSERT INTO table1 (x, mod10) VALUES (3, 1)
INSERT INTO table1 (x, mod10) VALUES (4, 2)
INSERT INTO table1 (x, mod10) VALUES (5, 5)
INSERT INTO table1 (x, mod10) VALUES (6, 6)
INSERT INTO table1 (x, mod10) VALUES (7, 7)
INSERT INTO table1 (x, mod10) VALUES (8, 8)
INSERT INTO table1 (x, mod10) VALUES (9, 9)
INSERT INTO table1 (x, mod10) VALUES (10, 0)

-- insert second batch of data
INSERT INTO table1 (x, mod10) VALUES (11, NULL)
INSERT INTO table1 (x, mod10) VALUES (12, NULL)
INSERT INTO table1 (x, mod10) VALUES (13, NULL)
INSERT INTO table1 (x, mod10) VALUES (14, NULL)
INSERT INTO table1 (x, mod10) VALUES (15, NULL)
INSERT INTO table1 (x, mod10) VALUES (16, NULL)
INSERT INTO table1 (x, mod10) VALUES (17, NULL)
INSERT INTO table1 (x, mod10) VALUES (18, NULL)
INSERT INTO table1 (x, mod10) VALUES (19, NULL)
INSERT INTO table1 (x, mod10) VALUES (20, NULL)

I want to update the mod10 column in the rows where mod10 is null, and the values I want to use should come from earlier entries in the table. Here's the query I tried:

UPDATE table1
SET mod10 = t.mod10
FROM table1 t
WHERE table1.x = t.x+10
AND table1.mod10 IS NULL

Looks like it should work just fine, but I get errors like

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'table1' does not match with a table name or alias name used in the query.

It took me a while to figure this out, but there is a sneaky way around this:

UPDATE table1
SET mod10 = t.mod10
FROM (SELECT * FROM table1) AS t
WHERE table1.x = t.x+10
AND table1.mod10 IS NULL

No comments: