May 9, 2008 at 3:57 am
Hi,
For some reason Im really struggling with this.
I have a table called Census_Access_Status_DB and in it there is a file name for example 8302057_SC2_830LL08_002.xml
the 002 shows the version of the file and we may have numerous files in for each school.
This query brings back a count of the files without the version number and creates information for the column Older_Version_exists
SELECT CAST(COUNT(*)AS VARCHAR (20))+' '+ 'File returned'
FROM dbo.Census_Access_Status_DB
GROUP BY Substring(FileName,1,19)
For example data returned for this would be...
1 File returned
1 File returned
2 File returned
1 File returned
1 File returned
Im now wanting to update this column for every row based on this information....
UPDATE dbo.Census_Access_Status_DB
SET Older_Version_exists =
(SELECT CAST(COUNT(*)AS VARCHAR (20))+' '+ 'File returned'
FROM dbo.Census_Access_Status_DB
GROUP BY Substring(FileName,1,19))
I get the error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
I think Im missing something quite simple but Ive had a day off I seem to have lost the ability to think in SQL :blink:
Is there another way I can sort the update statement so it works?
Ta
Debbie
May 9, 2008 at 4:58 am
Sorted.
I went a completely different route
UPDATE Census_Access_Status_DB
SET Older_Version_Exists = 1
WHERE FileName IN
(SELECT a.Filename
FROM dbo.Census_Access_Status_DB a
INNER JOIN Census_Access_Status_DB b
--Join on the filename without the file version
ON Substring(a.FileName,1,19)=Substring(b.FileName,1,19)
LEFT OUTER JOIN Census_Access_Status_DB c
ON Substring(a.FileName,1,19)=Substring(c.FileName,1,19)
GROUP BY a.File_ID, a.Date, a.Time, a.DFES, a.Filename, a.FileVersion,
c.fileversion
/*Brings back the max file_Version */
HAVING MAX(b.FileVersion) = a.fileVersion
/*Brings back any max file with another older file in the list. These
can be set to Y for Older_Version_Exists*/
AND c.fileversion <> a.fileVersion
)
May 9, 2008 at 4:58 am
Hi
this is not tested, but should be possible
UPDATE dbo.Census_Access_Status_DB
SET Older_Version_exists = iit.FileR
FROM dbo.Census_Access_Status_DB ot inner join
(SELECT SUBSTRING(it.FileName,1,19) AS NewFileName
, CONVERT(VARCHAR,COUNT(*)) +' File returned' AS FileR
FROM Census_Access_Status_DB
GROUP BY 1
) iit
on ot.Substring(ot.FileName,1,19) = iit.NewFileName
W. Lengenfelder
May 9, 2008 at 5:00 am
Thanks for that,
Ill have a look at that too! 🙂
May 9, 2008 at 5:30 am
Just a quick follow-up. It looks like, based on the code you posted, that you're missing the concept of part of the UPDATE statement. You went inline on both your update statements instead of using the FROM clause. Do a check in the BOL on the UPDATE statement to see how the FROM clause is used. I think it'll help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply