SQL Server Performance Forum – Threads Archive

Update Self Join

Maybe I shouldn’t be trying this but…I want to use a self-join update to update records to conform with the values of other records in the same table. Table FOO:
FOO.id (idenity)
FOO.tag (int)
FOO.bar (int, null) Update FOO
set bar = b.bar
from FOO a, FOO b
where ( a.bar is null )
and ( b.bar is not null )
and ( a.tag = b.tag ); This fails; Query Analyzer says bar is ambiguous. Is there a syntax for this that would work? Incidentally if there is a syntax, what if there is more than one non-null value for bar? Thanks, Bob

---

try this ?
update a
set a.bar = b.bar
from
FOO a
INNER JOIN FOO b ON (a.tag = b.tag)
where
a.bar is null and
b.bar is not null

---

Here is the issue:
set bar = b.bar Should be: set a.bar = b.bar OR try what chappy has suggested. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Dilli, SQL Server objected to that syntax. However, what Chappy suggested did work. Thanks! Bob

---

It’s basically the same logic only thing is the standard join.
Also, when you define the alias for that table, you have to reference that with update and it will still work. I was just correcting where the error was but I rarely(may be never) join the way like this. Update a
set a.bar = b.bar
from FOO a, FOO b
where ( a.bar is null )
and ( b.bar is not null )
and ( a.tag = b.tag )
Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

The fun part is of course that the "set bar" syntax itself is not ambiguous, because the column before the = operator can only be a column from the table mentioned after the UPDATE keyword. Update a
set bar = b.bar
from FOO a, FOO b
where ( a.bar is null ) and ( b.bar is not null ) and ( a.tag = b.tag ) This is just one of those times when the parser comes up with a confusing error message.

---

]]>