Update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update query

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-21 : 16:07:51

Please help..
I am writing a update query which has to update the @prod2 table column prod1 with the values of @Upprod table column prod2.
Please correct the query if anything needs to be corrected in the below query.
there are nearly 1500 records to be updated below is sample data..

declare @Prod2 table
(

Csid bigint,
Prod1 numeric(5)
)
insert into @Prod2
select 4103, 1946 union all
select 4101, 1956 union all
select 4102, 1904

select * from @prod2 order by csid

declare @Upprod table
(
Csid int,
Prod2 numeric(5)
)

insert into @Upprod
select 4102, 1956 union all
select 4103, 1936 union all
select 4101, 1964

select * from @Upprod order by csid

update @prod2 set prod1=Prod2 from @prod2 A,@Upprod B
Where A.csid=B.csid

select * from @prod2 order by csid

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-21 : 16:25:38

Your userid indicates you are a DBA, so you should start using join syntax:

update A
set prod1 = b.Prod2
from @prod2 A
inner join @Upprod B
on A.csid = B.csid

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources