You are right about that. I tried it on MS with another field giving a unique complex key.
The denormalized tables are confusing the hell out of me and I may be making the a big assumption about what the 'Y' flag really means. Since, the system puts the Y there, that is, there is no corresponding column in the actual DB , (only in the interface tables that I am working with ), I am assuming the system is providing with this flag to avoid the "more than one record " problems of which you speak. Thanks for your patience. On Tue, 18 Jan 2005 21:12:50 +0100, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > Frank Mamone wrote: > > > > ROW_ID > > CON_PERSON_UID > > PC_STATUS > > CON_CUST_STATUS > > CON_PR_POSTN > > > > I need to put the value of PC_STATUS into CON_CUST_STATUS where > > CON_PERSON_UID matches ROW_ID and marked as CON_PR_POSTN = 'Y' > > > > Remember this is denormalized data. So, essentially I need to do a > > self join update. With MS SQL I would use this > > > > UPDATE EIM_CONTACT > > SET EIM_CONTACT.CON_CUST_STAT_CD = EIM_CONTACT1.PC_STATUS > > FROM EIM_CONTACT , EIM_CONTACT EIM_CONTACT1 > > WHERE EIM_CONTACT.ROW_ID = EIM_CONTACT1.CON_PERSON_UID > > AND CON_PR_POSTN = 'Y' > > If that works you need to file a bug with Microsoft. Imagine the > following dataset: > > ROW_ID CON_PERSON_UID PC_STATUS CON_CUST_STATUS CON_PR_POSTN > x1x ? Y > ad x1x 2 Y > df x1x 3 Y > > The outcome of your update query on this dataset is undefined. > Should it set CON_CUST_STATUS in the first row to 2 or to 3? > > If you make the database choose consistently between the 2 and > the 3 (usually through aggregate functions) you have solved the > "Single row sub-query returns more than one row." error message > and then it will work. > > Jochem > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190983 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
