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