Convert Select Statement to Update

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)

Convert Select Statement to Update

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2008-10-22 : 09:07:45

Hi all i posted this on another post but i think it all got a little confsuing.

I have the following code which works fine as a select statement but i need to convert is to a update statement.

SELECT * FROM
(
SELECT IND_QUAL_REF,
COMMENTS,
CAST('NGCZ'+RIGHT(CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)),4) AS TEXT) AS NEW_COMMENTS
FROM INDIVIDUAL_QUAL
INNER JOIN
View_Integra_Reporting_tblIntegra_Scheme_Link
ON INDIVIDUAL_QUAL.COURSE_CODE = View_Integra_Reporting_tblIntegra_Scheme_Link.IntegraCode
WHERE SchemeID=35
AND COURSE_CODE >'300'
AND CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)) like 'NGC2%'
UNION ALL
SELECT IND_QUAL_REF,
COMMENTS,
CAST('NGPZ'+RIGHT(CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)),4) AS TEXT) AS NEW_COMMENTS
FROM INDIVIDUAL_QUAL
INNER JOIN
View_Integra_Reporting_tblIntegra_Scheme_Link
ON INDIVIDUAL_QUAL.COURSE_CODE = View_Integra_Reporting_tblIntegra_Scheme_Link.IntegraCode
WHERE SchemeID=35
AND COURSE_CODE >'300'
AND CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)) like 'NGP2%'
UNION ALL
SELECT IND_QUAL_REF,
COMMENTS,
CAST('NGC0'+RIGHT(CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)),4) AS TEXT) AS NEW_COMMENTS
FROM INDIVIDUAL_QUAL
INNER JOIN
View_Integra_Reporting_tblIntegra_Scheme_Link
ON INDIVIDUAL_QUAL.COURSE_CODE = View_Integra_Reporting_tblIntegra_Scheme_Link.IntegraCode
WHERE SchemeID=35
AND COURSE_CODE >'300'
AND CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)) like 'ngc0%'
UNION ALL
SELECT IND_QUAL_REF,
COMMENTS,
CAST('NGP0'+RIGHT(CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)),4) AS TEXT) AS NEW_COMMENTS
FROM INDIVIDUAL_QUAL
INNER JOIN
View_Integra_Reporting_tblIntegra_Scheme_Link
ON INDIVIDUAL_QUAL.COURSE_CODE = View_Integra_Reporting_tblIntegra_Scheme_Link.IntegraCode
WHERE SchemeID=35
AND COURSE_CODE >'300'
AND CAST(INDIVIDUAL_QUAL.COMMENTS AS VARCHAR(10)) like 'ngp0%'
) Dtbl

Kind Regards

Pete.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-10-22 : 09:18:05

hi
wat u mean by converting select to update query?
is there any issue with ur update query?

i am not getting your point....

reply with details...

petek
Posting Yak Master

192 Posts

Posted - 2008-10-22 : 09:28:52

Hi thanks for the prompt reply.

I have the statement above which returns the data i need into a derived table (dtbl).

I need to use the statement to update the following column:

INDIVIDUAL_QUAL.COMMENTS

Kind Regards

Pete.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-10-22 : 09:36:37

Assuming your rather verbose query reduces to:

SELECT Q.IND_QUAL_REF
    ,Q.COMMENTS
    ,LEFT(CAST(Q.COMMENTS AS VARCHAR(10)), 4) + RIGHT(CAST(Q.COMMENTS AS VARCHAR(10)),4) AS NEW_COMMENTS
FROM INDIVIDUAL_QUAL Q
    JOIN View_Integra_Reporting_tblIntegra_Scheme_Link L
        ON Q.COURSE_CODE = L.IntegraCode
WHERE L.SchemeID = 35 -- This could be Q.SchemeID
    AND Q.COURSE_CODE > '300'
    AND LEFT(CAST(Q.COMMENTS AS VARCHAR(10)), 4) IN ('NGC2', 'NGP2', 'NGC0', 'NGP0')

Then try something like:

UPDATE INDIVIDUAL_QUAL
SET COMMENTS = LEFT(CAST(COMMENTS AS VARCHAR(10)), 4) + RIGHT(CAST(COMMENTS AS VARCHAR(10)),4)
WHERE COURSE_CODE > '300'
    AND LEFT(CAST(COMMENTS AS VARCHAR(10)), 4) IN ('NGC2', 'NGP2', 'NGC0', 'NGP0')
    AND EXISTS
    (
        SELECT *
        FROM View_Integra_Reporting_tblIntegra_Scheme_Link L
        WHERE L.IntegraCode = INDIVIDUAL_QUAL.COURSE_CODE
            AND L.SchemeID = 35
    )

petek
Posting Yak Master

192 Posts

Posted - 2008-10-22 : 09:48:56

Hi Ifor,

the select statement returns the resluts as the code i pasted earlier but cleaner.

but baiscally it needs to change the values on comments from lowercase to uppercase
based on my search criteria.

IND_QUAL_REF COMMENTS NEW COMMENTS
360241 NGC00210 NGC00210
360242 NGC00206 NGC00206
362019 ngc00109 ngc00109
362020 ngc00111 ngc00111

Kind Regards

Pete.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-22 : 09:52:15

http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

petek
Posting Yak Master

192 Posts

Posted - 2008-10-22 : 10:03:55

my select statement returns the following result:

360235 NGC20025 NGCZ0025
360236 ngc20028 NGCZ0028
360217 NGC20027 NGCZ0027
360218 NGC20026 NGCZ0026
360232 ngc20018 NGCZ0018

it changes the case to UPPERCASE and replaces the number 2 to a Z.

baiscally the third column is the result i want to update with

Kind Regards

Pete.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-10-22 : 10:30:57

[code]UPDATE INDIVIDUAL_QUAL
SET COMMENTS = UPPER(LEFT(CAST(COMMENTS AS VARCHAR(8)), 3)) + 'Z' + RIGHT(CAST(COMMENTS AS VARCHAR(8)),4)
WHERE COURSE_CODE > '300'
AND LEFT(CAST(COMMENTS AS VARCHAR(8)), 4) IN ('NGC2', 'NGP2', 'NGC0', 'NGP0')
AND EXISTS
(
SELECT *
FROM View_Integra_Reporting_tblIntegra_Scheme_Link L
WHERE L.IntegraCode = INDIVIDUAL_QUAL.COURSE_CODE
AND L.SchemeID = 35
)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources