Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Update with subquery |
Sat, Mar 14 2009 10:26 AM | Permanent Link |
"James Relyea" | I'm losing my mind at times it seems. I have a pretty simple update
statement with a subquery. The subquery always returns 1 row but the table never gets updated. update tPhNums set intDeptId = (select intDeptId from tDepts where tDepts.idDeptId=tPhNums.idPhNumId); "int" fields are integers, "id" fields are GUIDs. The last few times I executed it, I got "ElevateDB Error #9999 Access violation at address 00403EAA in module 'edbsrvr.exe'. Read of address 00000004". I'm using b10 C/S. If I pick a valid GUID from tPhNums, and use that specifically, it does not update tPhNums either. select idDeptId from tDepts where tDepts.idDeptId='{D39C7B41-F6EA-4695-BD53-A33C7664886B}' <<< this always returns 1 row Yet, the following update does not update tPhNums either. update tPhNums set intDeptId = (select intDeptId from tDepts where tDepts.idDeptId='{D39C7B41-F6EA-4695-BD53-A33C7664886B}' ); I'm going to shut everything down and keep trying if the AV clears up. Does anyone see something that I'm having a brain hiccup over? I'm on hour #9 and going nowere. I don't want to build a cursor and iterate through the rows because that's overkill I think. Thanks!! jr |
Sat, Mar 14 2009 10:43 AM | Permanent Link |
"James Relyea" | I disconnected all connections from the server cleanly, and restarted the
ElevateDB server. And it partly works. I am concerned why it always failed, created AVs until I had to disconnect and restart the server though... This now works: > select idDeptId > from tDepts > where tDepts.idDeptId='{D39C7B41-F6EA-4695-BD53-A33C7664886B}' It is updating tPhNums but setting the intPhNumId field to null... not what I'm expecting because (and why I think I'm partly brain dead at times!). Should this syntax update tPhNums to the value in tDepts only on the rows that have matching idDeptId field values? (this is my intent): > update tPhNums > set intDeptId = > (select intDeptId > from tDepts > where tDepts.idDeptId=tPhNums.idPhNumId); This is the syntax I'm more familiar with, and I already know I can't use it with ElevateDB: update tPhNums set intDeptId=tDepts.intDeptid from tPhNums inner join tDepts on tPhNums.idDeptid = tDepts.idDeptid I appreciate any suggestions/feedback. jr |
Mon, Mar 16 2009 3:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | James,
<< The last few times I executed it, I got "ElevateDB Error #9999 Access violation at address 00403EAA in module 'edbsrvr.exe'. Read of address 00000004". I'm using b10 C/S. >> Could you send me the database catalog and tables that you're using ? I'll need to run that query to see if I can reproduce the AV. << If I pick a valid GUID from tPhNums, and use that specifically, it does not update tPhNums either. >> Same situation - I'll have to check it out with your data to see what the issue is. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Mar 16 2009 3:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | James,
<< It is updating tPhNums but setting the intPhNumId field to null... not what I'm expecting because (and why I think I'm partly brain dead at times!). Should this syntax update tPhNums to the value in tDepts only on the rows that have matching idDeptId field values? (this is my intent): >> Yes, that should be the case. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Mar 20 2009 1:33 PM | Permanent Link |
"James Relyea" | > Could you send me the database catalog and tables that you're using ? > I'll > need to run that query to see if I can reproduce the AV. I can't reproduce it often enough to make it worthwhile pursuing. > > << If I pick a valid GUID from tPhNums, and use that specifically, it does > not update tPhNums either. >> > > Same situation - I'll have to check it out with your data to see what the > issue is. I initially thought it was my problem, and while building the test db I'm convinced it is a "me issue". I'll work thru it and see where I went wrong. No need to pester you with it. Thanks though. jr |
Fri, Mar 20 2009 2:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | James,
<< I initially thought it was my problem, and while building the test db I'm convinced it is a "me issue". I'll work thru it and see where I went wrong. No need to pester you with it. >> Well, if you need me to look at anything at all, just let me know. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |