Update query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Update query

  1. #1
    rick Guest

    Update query


    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.

  2. #2
    Adelle Hartley Guest

    Re: Update query

    "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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center