How best to update a record from another record in the same table?

Discussion in 'microsoft.public.sqlserver.server' started by John Peterson, Aug 9, 2003.

  1. (SQL Server 2000, SP3)

    Hello all!

    I have a seemingly easy question that seems to stump me whenever I come across it, and was
    hoping to get some clarification.

    Consider the following:

    create table Test (Id int, Value1 sysname, Value2 sysname)
    go

    insert into Test values (1, 'Test1', 'Another1')
    insert into Test values (2, 'Test2', 'Another2')
    go

    I would like to update the first record with some of the fields in the second record.

    Part of my confusion lies in the fact that you can't alias the table to be UPDATEd. My
    first attempt looks something like this:

    update Test
    set Test.Value2 = Value2
    from Test as t2
    where Test.Id = 1
    and t2.Id = 2

    Which gives me the following error:

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

    So I tried to remove the Test reference in the WHERE clause:

    update Test
    set Test.Value2 = Value2
    from Test as t2
    where Id = 1
    and t2.Id = 2

    And this gives me 0 records affected -- perhaps like it's using the t2.Id implicitly.

    I could do something like this:

    update Test
    set Test.Value2 = (select Value2 from Test where Id = 2)
    where Id = 1

    And that works, but I'd hate to reference Test for as many times as I might have fields to
    update in the SET clause.

    I'd appreciate any pointers that anyone might be able to provide! :)

    John Peterson
     
    John Peterson, Aug 9, 2003
    #1
    1. Advertisements

  2. John Peterson

    oj Guest

    Either one would do...

    update t1
    set Value2=t2.Value2
    from Test t1, Test t2
    where t1.Id=1 and t2.Id=2

    update t1
    set Value2=t2.Value2
    from Test t1 join Test t2
    on t1.Id=1 and t2.Id=2
     
    oj, Aug 9, 2003
    #2
    1. Advertisements

  3. Thanks, oj!

    I didn't realize that you could do an alias after the UPDATE, and then specify the actual
    table name in the FROM clause. Very interesting!

    Thanks again!

    John Peterson
     
    John Peterson, Aug 9, 2003
    #3
  4. John Peterson

    oj Guest

    You're welcome, John.

    I believe you can find the example and some info regarding this syntax in
    sql book online under "update".
     
    oj, Aug 10, 2003
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.