August 14, 2008 at 8:10 am
I am trying to do an update on a table with this command:
update paridtest set parcel_id = parcel_id+
(select secs =
case
when b.dir = 'North' and len(rtrim(ltrim(str(sect)))) = 1
then a.parcel_id+'00'+ltrim(rtrim(str(a.sect + 36)))+'-'
when b.dir = 'North' and len(rtrim(ltrim(str(sect)))) = 2
then a.parcel_id+'0'+ltrim(rtrim(str(a.sect + 36)))+'-'
when b.dir is null and len(rtrim(ltrim(str(sect)))) = 1
then a.parcel_id+'00'+ltrim(rtrim(str(a.sect)))+'-'
when b.dir is null and len(rtrim(ltrim(str(sect)))) = 2
then a.parcel_id+'0'+ltrim(rtrim(str(a.sect)))+'-'
end
from paridtest a inner join pdtr b on
a.town = b.twp and a.rang = b.rng)
When I run it I get this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
When I run just the select part it runs fine and gives the right result set. Is there a way to redo this query so I don't get an error?
Thanks
Quinn
August 14, 2008 at 10:00 am
This is a common problem people have. Here are some options:
[font="Courier New"]UPDATE paridtest
SET parcel_id = parcel_id+CASE
WHEN b.dir = 'North' AND LEN(RTRIM(LTRIM(STR(sect)))) = 1 THEN a.parcel_id+'00'+LTRIM(RTRIM(STR(a.sect + 36)))+'-'
WHEN b.dir = 'North' AND LEN(RTRIM(LTRIM(STR(sect)))) = 2 THEN a.parcel_id+'0'+LTRIM(RTRIM(STR(a.sect + 36)))+'-'
WHEN b.dir IS NULL AND LEN(RTRIM(LTRIM(STR(sect)))) = 1 THEN a.parcel_id+'00'+LTRIM(RTRIM(STR(a.sect)))+'-'
WHEN b.dir IS NULL AND LEN(RTRIM(LTRIM(STR(sect)))) = 2 THEN a.parcel_id+'0'+LTRIM(RTRIM(STR(a.sect)))+'-'
END
FROM
paridtest a INNER JOIN
pdtr b ON
a.town = b.twp AND
a.rang = b.rng
[/font]
or
[font="Courier New"]UPDATE paridtest
SET parcel_id = parcel_id + (SELECT
CASE
WHEN b.dir = 'North' AND LEN(RTRIM(LTRIM(STR(sect)))) = 1 THEN parcel_id+'00'+LTRIM(RTRIM(STR(sect + 36)))+'-'
WHEN b.dir = 'North' AND LEN(RTRIM(LTRIM(STR(sect)))) = 2 THEN parcel_id+'0'+LTRIM(RTRIM(STR(sect + 36)))+'-'
WHEN b.dir IS NULL AND LEN(RTRIM(LTRIM(STR(sect)))) = 1 THEN parcel_id+'00'+LTRIM(RTRIM(STR(sect)))+'-'
WHEN b.dir IS NULL AND LEN(RTRIM(LTRIM(STR(sect)))) = 2 THEN parcel_id+'0'+LTRIM(RTRIM(STR(sect)))+'-'
END
FROM
pdtr b
WHERE
town = b.twp AND
rang = b.rng)
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 14, 2008 at 10:31 am
Thanks Jack for the timely responce!
Both of those work just fine.
I had it pretty close.
Thanks again
Quinn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply