update statement - 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 statement

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2008-10-01 : 09:44:57

hello all,

firstly thanks for looking at my post.

i need help on a update statement.

I've built a 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

BASICALLY.........

For each row (IND_QUAL_REF) in the INDIVIDUAL_QUAL table, I want the COMMENTS field updated to NEW_COMMENTS

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:53:24

We could help you but we have no idea form which table your columns derive from.
Prefix your columns with table name and we might be able to help you.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:57:45

You don't need 4 UNIONed queries here.
It will suffice with just one query like this

SELECT		{iq | sl}.IND_QUAL_REF,
		iq.COMMENTS,
		SUBSTRING(iq.COMMENTS, 1, 4) + SUBSTRING(iq.COMMENTS, 7, 4) AS NEW_COMMENTS
FROM		INDIVIDUAL_QUAL AS iq
INNER JOIN	View_Integra_Reporting_tblIntegra_Scheme_Link AS sl ON sl.IntegraCode = iq.COURSE_CODE
WHERE		{iq | sl}.SchemeID = 35
		AND {iq | sl}.COURSE_CODE > '300'
		AND iq.COMMENTS LIKE 'ng[cp][20]%'

E 12°55'05.63"
N 56°04'39.26"

petek
Posting Yak Master

192 Posts

Posted - 2008-10-01 : 10:08:26

Hi Peso,

I only have one table and one view, the SchemeID is the only field in the view, everything else is from the INDIVIDUAL_QUAL table.

as for your reply thanks but what is [iq | sl].

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:13:23

Yes, but in your view you query the table 4 times. No need for that.
Use my suggestion in the view instead.

{iq | sl} are the missing table aliases from your query.
Prefix your column with either iq. or sl. to make your query readable so that we know from which table each and one column derives from.

E 12°55'05.63"
N 56°04'39.26"

petek
Posting Yak Master

192 Posts

Posted - 2008-10-01 : 10:25:51

Peso me again where does the update statement go?

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:28:03

How can I know?
You have to post the edited query after putting a valid table alias in front of the marked columns in red.

E 12°55'05.63"
N 56°04'39.26"

petek
Posting Yak Master

192 Posts

Posted - 2008-10-01 : 10:42:53

SELECT iq.IND_QUAL_REF,
iq.COMMENTS,
SUBSTRING(iq.COMMENTS, 1, 4) + SUBSTRING(iq.COMMENTS, 7, 4) AS NEW_COMMENTS
FROM INDIVIDUAL_QUAL AS iq
INNER JOIN View_Integra_Reporting_tblIntegra_Scheme_Link AS sl ON sl.IntegraCode = iq.COURSE_CODE
WHERE sl.SchemeID = 35
AND iq.COURSE_CODE > '300'
AND iq.COMMENTS LIKE 'ng[cp][20]%'

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:52:08

Now, if this query produces all the records you want to UPDATE

SELECT		iq.IND_QUAL_REF,
		iq.COMMENTS,
		REPLACE(SUBSTRING(iq.COMMENTS, 1, 4), '2', 'Z') + SUBSTRING(iq.COMMENTS, 7, 4) AS NEW_COMMENTS
FROM		INDIVIDUAL_QUAL AS iq
INNER JOIN	View_Integra_Reporting_tblIntegra_Scheme_Link AS sl ON sl.IntegraCode = iq.COURSE_CODE
WHERE		sl.SchemeID = 35
		AND iq.COURSE_CODE > '300'
		AND iq.COMMENTS LIKE 'ng[cp][20]%'

you can run this UPDATE query to perform the changes

UPDATE		iq
SET		iq.COMMENTS = REPLACE(SUBSTRING(iq.COMMENTS, 1, 4), '2', 'Z') + SUBSTRING(iq.COMMENTS, 7, 4)
FROM		INDIVIDUAL_QUAL AS iq
INNER JOIN	View_Integra_Reporting_tblIntegra_Scheme_Link AS sl ON sl.IntegraCode = iq.COURSE_CODE
WHERE		sl.SchemeID = 35
		AND iq.COURSE_CODE > '300'
		AND iq.COMMENTS LIKE 'ng[cp][20]%'

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 10:53:56

quote:
Originally posted by petek

SELECT iq.IND_QUAL_REF,
iq.COMMENTS,
SUBSTRING(iq.COMMENTS, 1, 4) + SUBSTRING(iq.COMMENTS, 7, 4) AS NEW_COMMENTS
FROM INDIVIDUAL_QUAL AS iq
INNER JOIN View_Integra_Reporting_tblIntegra_Scheme_Link AS sl ON sl.IntegraCode = iq.COURSE_CODEWHERE sl.SchemeID = 35
AND iq.COURSE_CODE > '300'
AND iq.COMMENTS LIKE 'ng[cp][20]%'

Kind Regards

Pete.

whats the purpose of join. you're retrieving only fields from INDIVIDUAL_QUAL. then why join? i think you need just a select from INDIVIDUAL_QUAL .

Now for your mail query (the update) i think you need just this

UPDATE INDIVIDUAL_QUAL 
SET COMMENTS=SUBSTRING(COMMENTS, 1, 4) + SUBSTRING(COMMENTS, 7, 4)

petek
Posting Yak Master

192 Posts

Posted - 2008-10-01 : 10:55:19

thanks sooooo much for your help.

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:55:34

quote:
Originally posted by visakh16

Now for your mail query (the update) i think you need just this

UPDATE INDIVIDUAL_QUAL
SET COMMENTS=SUBSTRING(COMMENTS, 1, 4) + SUBSTRING(COMMENTS, 7, 4)

No WHERE clause either?

And there is a purpose for having the JOIN. What if some record in iq table doesn't have a related record in sl table over IntegraCode vs CourseCode?
Especially since the WHERE clause explicitly tells us to filter for sl.schemeID column...

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 10:59:11

And if your update needs to be done only if it has matches in other table then use this


UPDATE iq
SET COMMENTS=SUBSTRING(COMMENTS, 1, 4) + SUBSTRING(COMMENTS, 7, 4)
FROM INDIVIDUAL_QUAL  iq
WHERE EXISTS(SELECT * FROM View_Integra_Reporting_tblIntegra_Scheme_Link AS sl 
WHERE sl.IntegraCode = iq.COURSE_CODEWHERE sl.SchemeID = 35
AND sl.SchemeID = 35)
AND iq.COURSE_CODE > '300'
AND iq.COMMENTS LIKE 'ng[cp][20]%'

petek
Posting Yak Master

192 Posts

Posted - 2008-10-22 : 08:40:07

Hi all again,

I am still experiencing probelms with this code.....
i need to search my view and update data based on my where clause.

basically it needs to change the case of the records to uppercase aswell.

Kind Regards

Pete.

petek
Posting Yak Master

192 Posts

Posted - 2008-10-22 : 08:44:57

sorry i should have added....

when i run the select statement above it returns the result as i expect. but how do i convert this to and update statement???

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 10:25:30

I posted that already way back in 10/01/2008 : 10:52:08

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources