Solved

Change a field value within Update Select

Posted on 2008-06-17
3
698 Views
Last Modified: 2012-08-13
I'm wondering if I can combine the following two queries to run as one query.  The first query is an Insert/select:  The second query is an update based on one of the fields being inserted.  The field is StatusCode.  It gets inserted with an initial value of 1 but then it needs to be changed based on the values of some other fields in the query.  Is it possible to do this all in one query?  

----- 1st query

insert into FileOne
    (CNumber,
    ID,
    DCode,
    PCode,
    b.Indicator,
    b.Discount,
    StatusCode)
SELECT
    cast(a.CNumber as char (5)),
    cast(a.ID as varchar (16)),
    cast(a.DCode as varchar (5)),
    b.PCode,
    b.Indicator,
    b.Discount,
    1
FROM PFILE a
    INNER JOIN TFile b
        ON a.ID = b.ID
WHERE SUBSTRING(b.CNumber,1,1)<>'S'

---- 2nd query

UPDATE FileOne
SET StatusCode = 0
WHERE
 (indicator = 0
OR Pcode <> 'D'
OR Dcode <>'C'
OR discount = 1)
0
Comment
Question by:dkcoop03
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
No, you cannot.  In 2008, a MERGE statement can do something similar to what you're doing, but for right now you have to use seperate statements....INSERT and UPDATE are distinct operations.
0
 
LVL 13

Accepted Solution

by:
rickchild earned 250 total points
Comment Utility
Try this:
insert into FileOne

    (CNumber,

    ID,

    DCode,

    PCode,

    b.Indicator,

    b.Discount,

    StatusCode) 

SELECT

    cast(a.CNumber as char (5)),

    cast(a.ID as varchar (16)),

    cast(a.DCode as varchar (5)),

    b.PCode,

    b.Indicator,

    b.Discount,

    CASE

        WHEN (b.indicator = 0 OR b.Pcode <> 'D' OR cast(a.Dcode as varchar(5)) <>'C' OR discount = 1) THEN 0

    ELSE 1

    END as StatusCode

FROM PFILE a

    INNER JOIN TFile b

        ON a.ID = b.ID

WHERE SUBSTRING(b.CNumber,1,1)<>'S'

Open in new window

0
 

Author Closing Comment

by:dkcoop03
Comment Utility
Thanks, that works great!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now