Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 12 total |
Another SQL Question - Update table data from another table |
Wed, May 29 2013 7:58 PM | Permanent Link |
Adam H. | 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. |
Thu, May 30 2013 1:32 AM | Permanent Link |
Barry | Adam,
this works for me: update _Tmp2 T Set T.ParentID = (Select ID From Names where MatchingField = T.Matchingfield) Barry |
Thu, May 30 2013 3:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Thu, May 30 2013 3:50 AM | Permanent Link |
Adam H. | 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) |
Thu, May 30 2013 11:19 AM | Permanent Link |
Barry | 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 |
Thu, May 30 2013 9:35 PM | Permanent Link |
Adam H. | 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 > |
Thu, May 30 2013 9:35 PM | Permanent Link |
Adam H. | 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 |
Fri, May 31 2013 4:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sun, Jun 2 2013 7:14 PM | Permanent Link |
Adam H. | 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. |
Tue, Jun 4 2013 3:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |