Update query

[rick]

The idea behind the me update query is to take data from one record and update
a different record.

Sample query:
update tblcostcodedetl2 SET fltLabor = c2.fltLabor, fltMatl = c2.fltMatl
from tblcostcodedetl2 c2
inner join tblcostcodedetl1 c1 on c2.intcostcdid = c1.intcostcdid
and c2.inttiid = 559639 and c1.chrcostcd in ('900000')
where c2.inttiid = 1001 and c1.chrcostcd in ('900000')

The above sample is provided for conveying the concept I am trying to achieve.
In the above sample I am updating only 2 field but in the real query I am
updating 20 fields. I want to take fltLabor and fltMatl from record with
ID 559639 and update fltLabor and fltMatl record with ID 1001. The record
structure is identical as the two records reside in the same table (tblCostCodeDetl2).

I do not want to use a #temp table if possible (I already know how).
I do not want to use multipe select statement (I already know how).
I would like a solution that would do the update in one shot.

[Adelle Hartley]

"rick" <rpassaglia@pepperconstruction.com> wrote in message
news:3aface64$1@news.devx.com...
>
> The idea behind the me update query is to take data from one record and
update
> a different record.
>
> Sample query:
> update tblcostcodedetl2 SET fltLabor = c2.fltLabor, fltMatl = c2.fltMatl
> from tblcostcodedetl2 c2
> inner join tblcostcodedetl1 c1 on c2.intcostcdid = c1.intcostcdid
> and c2.inttiid = 559639 and c1.chrcostcd in ('900000')
> where c2.inttiid = 1001 and c1.chrcostcd in ('900000')

I'm not sure I have completely understood your table structure,
but here goes...

update tblcostcodedetl2 c1 SET fltLabor = c2.fltLabor, fltMatl = c2.fltMatl
from tblcostcodedetl2 c1, tblcostcodedetl2 c2
WHERE c2.inttiid = 559639 and c1.inttiid = 1001

HTH.

Adelle.

_____________________________________________________________
Impress your friends. Become an SQL guru.
http://www.sql-guru.com