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

(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 PetersonEither 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
RAC v2.2 & QALite!
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> (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
>|||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
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23Ya0TdsXDHA.652@.TK2MSFTNGP10.phx.gbl...
> 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
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > (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
> >
> >
>|||You're welcome, John.
I believe you can find the example and some info regarding this syntax in
sql book online under "update".
--
-oj
Rac v2.2 & QALite
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O9fn6fsXDHA.2204@.TK2MSFTNGP12.phx.gbl...
> 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
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23Ya0TdsXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > 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
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:%23GZxFZsXDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > > (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
> > >
> > >
> >
> >
>