469,111 Members | 1,944 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

Subqurey returns more than one value error HELP....

My update query updates about 50 columns and they all have the same
format: (Im using ADP to interact with the SQL Server)

UPDATE dbo.GIS_EVENTS_TEMP
SET VEH1TYPE =
(SELECT VEHICLETYPE
FROM VEHICLE AS A
WHERE(GIS_EVENTS_TEMP.CASEID = A.CRASHNUMBER) AND(A.UNITID = 1)),

VEH2TYPE = (....... same as above with UNITID = 2)
VEH1DAM = (Same format different tables...)
..
..
n

The problem is that it's part of an import process that is dependent on
data from an outside source which come once a month.
I found an error in one of the tables (a duplicate ID) which should not
be in the table this is causeing the "Subqurey returns more than one
value" error.

How can I guard against this..? If possible I would like it to only
return the first value..

I tried "COALESCE" but I couldn't get it to work I also tried "SELECT
DISTINCT"....

Any Ideas would be great.....

Jul 23 '05 #1
7 1287
AK
you might want to impose a unique index on (CRASHNUMBER,UNITID) and
clean up your data

you might also try

UPDATE dbo.GIS_EVENTS_TEMP
SET VEH1TYPE =
(SELECT MIN(VEHICLETYPE)
FROM VEHICLE AS A
WHERE(GIS_EVENTS_TEMP.CASEID = A.CRASHNUMBER) AND(A.UNITID = 1)),

so that it can work act as if the data were clean

BTW, GIS_EVENTS_TEMP violates 1NF

Jul 23 '05 #2
Worked great.... :)
I Know it does.. but I have a good reason...lol
The data comes in as a ralational structure with 16 tables. The data
needs to be used in a GIS enviorment which is very slow with
relationships. Sooo i'm taking the relational structure archiving it
and creating a flat file structure for the GIS.

Jul 23 '05 #3
AK
yet the fact that at least one of the subqueries blew up might indicate
that there is a problem with your data. For instanse, if there are 2
rows with the same (CRASHNUMBER,UNITID), it is dirty data, is it not?

Jul 23 '05 #4
Yes you are correct...it is dirty data. But it is the data that the
clients are getting and I can't change that. So I'm working within the
constraints of the data I have to work with. I could try to clean the
16 tables when I import them into the DB but it 'should' come to me
clean... but your right its not? I need a DB washer

Jul 23 '05 #5
> [...] I need a DB washer

You also need a database that can keep itself clean: i.e. create unique
constraints, normalize the tables, create foreign keys and check
constraints... and after you do a one-time cleaning of the existing
data, you won't need a DB washer anymore.

Razvan

Jul 23 '05 #6
AK
yes, that's understandable. I've been in your boots more then once.
unfortunately, the cleanliness of the data is frequently determined by
political decisions rather than technical ones

Jul 23 '05 #7
YuP..Yup..Thanks for the help.......AK

Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by tornado | last post: by
2 posts views Thread by leo2100 | last post: by
1 post views Thread by Rahul | last post: by
3 posts views Thread by squeek | last post: by
10 posts views Thread by satan | last post: by
Dormilich
2 posts views Thread by Dormilich | last post: by
2 posts views Thread by yeshello54 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.