473,326 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 1418
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: tornado | last post by:
Hi all, I am pretty new to PHP. I was reading PHP manual and trying out the example from 2nd chapter (A simple Tutorial). When i try to print the variable as given in the example it returns...
2
by: leo2100 | last post by:
Hi, I need help with this program. The program is supposed to take a text file and identify the words in it, then it should print them and count how many times a word is repeated. At first main...
1
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am...
3
by: squeek | last post by:
what i need help with determining functions how to word them and variables to consider A resistor is a circuit device designed to have a specific resistance value between its ends. Resistance...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
10
by: satan | last post by:
I need the definitions of the method copyQueue, the default constructor, and the copy constructor folr the class LinkedQueueClass. This is what i get so far public abstract class DataElement {...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
2
Dormilich
by: Dormilich | last post by:
Hi, I'm testing my classes for a web page and I stumble upon an error I don't have a clue what it means: Error: Fatal error: Can't use method return value in write context in "output.php" on...
2
by: yeshello54 | last post by:
so here is my problem...in a contact manager i am trying to complete i have ran into an error..we have lots of code because we have some from class which we can use...anyways i keep getting an error...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.