Hi Guys,

I'm trying to update results in a table with other results.

In DBISam I would have executed:

update _Tmp2 T
Set T.ParentID = N.ID
From _Tmp2 T
inner join Names N on (N.Matchingfield = T.MatchingField).

I'm trying to do this in EDB using the following:

update _Tmp2 T
Set (ParentID) = (Select ID From Names where MatchingField =
T.Matchingfield)

When I execute this I get the error:

ElevateDB Error #1011 An error occurred with the query (SELECT ALL "ID"
AS "ID" FROM "Names" WHERE "Matchingfield" = "T"."Matchingfield") (A
scalar query can only return a single value)

Just wondering if someone can show where I've gone wrong please?

Cheers

Adam.

Adam,

this works for me:

update _Tmp2 T
Set T.ParentID = (Select ID From Names where MatchingField = T.Matchingfield)

Barry

Adam


Its probably data. How many rows would your subselect return as a query - if its more than one there's your problem.

If its just one there's another problem

Roy Lambert [Team Elevate]

Hi Barry,

Thanks. I've changed to that - but I still get the same error.

(A scalar query can only return a single value).

> update _Tmp2 T
> Set T.ParentID = (Select ID From Names where MatchingField = T.Matchingfield)

Adam H.

As per Roy's suggestion. I assumed that the T.MatchingField returned only 1 field. In other words, an index on T.MatchingFIeld was either unique or primary.

If the subselect returns more than 1 value, what value do you want to use to update the row? If all of the values returned by the subselect are the same, then try this:

update _Tmp2 T
Set T.ParentID = (Select ID From Names where MatchingField = T.Matchingfield range 1 to 1)

Barry

Hi Barry,

Thanks for that - it looks like that was the issue in the end. The range
1 to 1 seems to have fixed the issue, although I'm not sure why there is
more than 1 returned row at present, but I guess that's for me to figure
out.

Cheers for the help!

Adam.

> Adam H.
>
> As per Roy's suggestion. I assumed that the T.MatchingField returned only 1 field. In other words, an index on T.MatchingFIeld was either unique or primary.
>
> If the subselect returns more than 1 value, what value do you want to use to update the row? If all of the values returned by the subselect are the same, then try this:
>
> update _Tmp2 T
> Set T.ParentID = (Select ID From Names where MatchingField = T.Matchingfield range 1 to 1)
>
> Barry
>

Hi Roy,

> Its probably data. How many rows would your subselect return as a
query - if its more than one there's your problem.


That seems to be the issue. Barry's suggestion ranging from 1 to 1 seems
to have fixed the issue.

Appreciate the help!

Adam

Adam

> > Its probably data. How many rows would your subselect return as a
>query - if its more than one there's your problem.
>
>
>That seems to be the issue. Barry's suggestion ranging from 1 to 1 seems
>to have fixed the issue.

I am the bearer of bad news - it hasn't fixed the problem - its hidden it. That may be good enough but you have to resolve the underlying problem.

From your reply to Barry I know you've already figured that out but its worth emphasising.

Out of interest how many rows were being returned and were they all the same?

Roy Lambert [Team Elevate]

Hi Roy,

> I am the bearer of bad news - it hasn't fixed the problem - its hidden it. That may be good enough but you have to resolve the underlying problem.

Actually, in this instance it should be OK. The original query data was
given from a customer to import into the application as a 'best as can'.
It effectively is a company / names list with linked contact details.
(Multiple contacts per customer).

The data that they provided was (to use a typical Aussie slang word) 'a
dogs breakfast' and contained multiple contacts of the same contact, and
multiple entries of the same company.

We trimmed the companies back to one record per company in our database,
so when importing the contacts, it looks like there were still multiple
of the same contacts (because their database had the same contact
entered more than once - for each of the same 'company' record they set
up (if that makes sense)).

So - truncating, or getting only the first entry to show should be OK.

They're aware that there may be mistakes - but it's a 'best as we can'
process considering the information we were given to start with.

I'm pretty sure I've done a similar thing with DBISam, but guess that
DBISam just truncates automatically, whereas EDB is a little more
precise - and I wasn't sure how to deal with it. Now I am.

I couldn't tell you how many of the rows were the same that were
returned, but from memory there were about 250 unique entries.

Cheers

Adam.

Adam


>The data that they provided was (to use a typical Aussie slang word) 'a
>dogs breakfast' and contained multiple contacts of the same contact, and
>multiple entries of the same company.

We have the same dogs here

>We trimmed the companies back to one record per company in our database,
>so when importing the contacts, it looks like there were still multiple
>of the same contacts (because their database had the same contact
>entered more than once - for each of the same 'company' record they set
>up (if that makes sense)).
>
>So - truncating, or getting only the first entry to show should be OK.

<wild cackling laughter> until they find out all the useful data was on the subsequent records - then they blame you </wild cackling laughter>

>They're aware that there may be mistakes - but it's a 'best as we can'
>process considering the information we were given to start with.

Having been involved in a few major dedups myself you're probably right. I've used a lot of different strategies and have yet to find one I really like (if that's the right word) apart from eyeballing the data.

In my business app I have routines that tries to prevent duplicates going in and a slew of routines to help check and identify those that slipped past the first check. Out of the 21k records I think there are more than a few duplicates left

>I'm pretty sure I've done a similar thing with DBISam, but guess that
>DBISam just truncates automatically, whereas EDB is a little more
>precise - and I wasn't sure how to deal with it. Now I am.
>
>I couldn't tell you how many of the rows were the same that were
>returned, but from memory there were about 250 unique entries.

Tell em to type the lot in again - it may teach them not to enter the same thing lots of times <vbg>


Roy Lambert