Als mir mein Kollege Thomas ein Problem präsentierte, musste ich erst mal lange überlegen. Grund genug das hier festzuhalten, falls es auch anderen so geht. Im UPDATE-Befehl kann man einen JOIN auf eine andere Tabelle machen. Der UPDATE wird aber dennoch nur auf die Basistabelle durchgeführt. Bei einem Self-Join kann dabei ein Fehler kommen:
UPDATE test
SET fill= t2.fill
FROM test AS t1
JOIN test as t2
ON t1.refid=t2.id
Meldung 8154, Ebene 16, Status 1, Zeile 1
Die 'test'-Tabelle ist mehrdeutig.
Oder auf englisch: "The table '%.*s' is ambiguous." Die Lösung besteht darin, dass man im UPDATE über den Alias deutlich macht, auf welche Tabelle der UPDATE durchgeführt werden soll. Beispielsweise so:
UPDATE t1 /* <-- Hier den Alias verwenden */
SET fill= t2.fill
FROM test AS t1
JOIN test as t2
ON t1.refid=t2.id
Das gilt freilich auch für ein DELETE-FROM.
Hier noch der Code für ein schnelles Repro:
USE tempdb
GO
IF object_id ('test') IS NULL
CREATE TABLE test (
id INTEGER NOT NULL PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
refid INTEGER NULL REFERENCES test(id),
fill INTEGER NULL);
ELSE
DELETE FROM test;
INSERT INTO test (id, name, refid, fill)
SELECT 1, N'Bill', NULL, 10 UNION ALL
SELECT 2, N'Bob', NULL, 20 UNION ALL
SELECT 3, N'Hank', NULL, 30 UNION ALL
SELECT 4, N'Tom', 1, NULL UNION ALL
SELECT 5, N'Babs', 1, NULL UNION ALL
SELECT 6, N'Grit', 1, NULL UNION ALL
SELECT 7, N'Alex', 2, NULL UNION ALL
SELECT 8, N'Eric', 2, NULL UNION ALL
SELECT 9, N'Stan', 2, NULL UNION ALL
SELECT 10, N'Ole', 2, NULL UNION ALL
SELECT 11, N'Skye', 3, NULL UNION ALL
SELECT 12, N'Mary', 3, NULL UNION ALL
SELECT 13, N'Joe', 3, NULL UNION ALL
SELECT 14, N'Kurt', 3, NULL UNION ALL
SELECT 15, N'Karl', 3, NULL;
GO
UPDATE test
SET fill= t2.fill
FROM test AS t1
JOIN test as t2
ON t1.refid=t2.id
/*
Meldung 8154, Ebene 16, Status 1, Zeile 1
Die 'test'-Tabelle ist mehrdeutig.
*/
GO
UPDATE t1
SET fill= t2.fill
FROM test AS t1
JOIN test as t2
ON t1.refid=t2.id