SQL Server Performance

Update Self Join

Discussion in 'SQL Server 2005 General Developer Questions' started by rwshep2000, Jan 31, 2007.

  1. rwshep2000 New Member

    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
  2. Chappy New Member

    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

  3. DilliGrg Member

    quote:Originally posted by rwshep2000

    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.

    Thanks,

    Bob

    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)

  4. rwshep2000 New Member

    Dilli,

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

    Bob
  5. DilliGrg Member

    quote:Originally posted by rwshep2000

    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)

  6. Adriaan New Member

    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.

Share This Page